Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Washington, DC Metro area
    Posts
    1

    Unanswered: help with VBA/DAO?

    Hi all,

    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:
    Code:
    Sub dbconnect3() 
    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
    
    'close recordset 
    rsODBCDirect.Close 
    wrkODBC.Close 
    Set rsODBCDirect = Nothing 
    Set wrkODBC = Nothing 
    Set conODBCDirect = Nothing 
    End Sub
    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.

    Does anyone have any ideas?
    Thanks,
    Richard

  2. #2
    Join Date
    May 2006
    Posts
    1

    Talking

    Hi All,

    I do have the same problem, could any one else can help?

    Please send to my e-mail junnytech@gmail.com

    Thx.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •