I am very new to VBA and DAO, but I am learning as fast as I can. I managed to get the following code put together from searching on google and the MS Knowledge Base, but it's not quite working correctly.
Here's my code:
Dim wkrJet As DAO.Workspace, wrkODBC As DAO.Workspace
Dim conODBCDirect As DAO.Connection
Dim rsODBCDirect As DAO.Recordset
Dim strConn As String
strConn = "ODBC;DATABASE=dbname;UID=username;PWD=password;DSN=dbname;LOGINTIMEOUT=50;"
Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set conODBCDirect = wrkODBC.OpenConnection("", , , strConn)
SqlStr = "SELECT CHANNEL_ID, CHANNEL_NAME FROM CHANNEL"
Set rsODBCDirect = conODBCDirect.OpenRecordset(SqlStr, dbOpenDynamic)
intMaxCol = rsODBCDirect.Fields.Count
intMaxRow = rsODBCDirect.RecordCount
Set objXL = New Excel.Application
_ _ With objXL
_ _ _ .Visible = True
_ _ _ Set objWkb = .Workbooks.Add
_ _ _ Set objSht = objWkb.Worksheets(1)
_ _ _ With objSht
_ _ _ _ .Range(.Cells(1, 1), .Cells(intMaxRow, _
_ _ _ _ _ _ intMaxCol)).CopyFromRecordset rsODBCDirect
_ _ _ End With
_ _ End With
Set rsODBCDirect = Nothing
Set wrkODBC = Nothing
Set conODBCDirect = Nothing
I have 2 problems:
• First, the above query should return over 10,000 results. Instead, I only see the first 100 results. No matter what query I write, I only see 100 results.
• Second, there is a complex query that I want to run in this code, but even though I know the query is correct for the ODBC connection being used, it will not execute. I keep getting the following error message: "Run-time error '3669': Execution Cancelled." I know this query is right, because I can copy it directly from my vba procedure and into MSQuery and it runs fine.
I am assuming that the 2 problems are related, and if we can solve the first, hopefully the second will be solved as well. The complex query would return a result set of about 40,000 to 50,000 rows.