retrieving data from ODBC source into Excel

[SabreWolf3]

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

 

[SabreWolf3]

Sorry, I am new to this forum. I clicked submit by accident, and before I knew it... my post had gone up without me asking my question. The above code has 2 problems:

• First, there should have been over 10,000 results returned from the query. When I run the query, it only takes about 2 seconds, but I get a listing of only 100 results.

• Second, I can only run simple queries in the code. If I put in more complex queries, I get the following error:
"Run-time error '3669': Execution cancelled." I know the queries are correct, because I can copy it from my code into an MSQuery window and it will run and return the correct results.

Any ideas? I am new to VBA coding and DAO, so I hope it's a simple answer. I got the code above from googling and the MS Knowledge Base.

Thanks,
Richard

 

[agnesja]

guy... can you help me in linking access to visual baic? sent me a full intrunction to make my database work... pls...

 

[jhall01]

Have you tried ADO instead of DAO? You don't have a parameter query so ADO is a little bit faster and more efficient.

But here is what I see:

You never close the conODBCDirect...you just set it to nothing. You should close the connection....this is no big deal.

But try using the following code to display your query:

Set rs = conODBCDirect.OpenRecordset(sqlstr)

For i = 0 To rs.Fields.Count - 1
objSht.Cells(1, i + 1).Value = objSht.Fields(i).Name
Next

objSht.Range(objSht.Cells(1, 1), objSht.Cells(1, rs.Fields.Count)).Font.Bold = True

objSht.Range("A2&quot.CopyFromRecordset rs

This breaks out the titles for you also and bolds them.

This basically does the same thing you were doing only it doesn't use the Maxrows and MaxCol for the area to fill with data. I think those to variables are causing the problems...plus you didn't dim them above as integers. repeat this montra VBA Montra:

"DIM, DIM, DIM"

Always declare your variables and assign a type. Try this out and see what it does.

if you don't want to bold your titles then just use this code:

Set rs = conODBCDirect.OpenRecordset(sqlstr)
objSht.Range("A2&quot.CopyFromRecordset rs

rs.close