By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

VBA/ADO recordset problem from Access query

P: 3
Hi,
I have a set of queries in access that I would like to import into excel. My problem is that there are no records are returned when the code is run, but I do get field names. This same code works if the query results are placed in a table, but to create tables for every query I need to get data from would be too much like work. Here is the code Iím using.

Expand|Select|Wrap|Line Numbers
  1. sSQL = "SELECT * FROM `Steady Customer for Chi_3-5bulk8`;"
  2. Sub ADO_Import(sSQL As String)
  3.     Dim cnn As ADODB.Connection
  4.     Dim rst As ADODB.Recordset
  5.     Dim MyConn
  6.     Dim lOffset As Long
  7.     Dim fld As Field    
  8.  
  9.     'Application.DisplayAlerts = False
  10.  
  11.     Columns("A:F").Select
  12.     Selection.ClearContents
  13.  
  14.  
  15.     MyConn = ď\\netshare\chris\DB\ABrt.mdb"
  16.     Set cnn = New ADODB.Connection
  17.     With cnn
  18.         .Provider = "Microsoft.Jet.OLEDB.4.0"
  19.         .Open MyConn
  20.     End With
  21.  
  22.     Set rst = New ADODB.Recordset
  23.     rst.CursorLocation = adUseServer
  24.     rst.Open Source:=sSQL, _
  25.              ActiveConnection:=cnn, _
  26.              LockType:=adLockReadOnly
  27.  
  28.     Range("A1").CurrentRegion.Offset(1, 0).Clear
  29.  
  30.     With Range("A1") 'create field headers
  31.     lOffset = 0
  32.         For Each fld In rst.Fields
  33.             .Offset(0, lOffset).Value = fld.Name
  34.             lOffset = lOffset + 1
  35.         Next fld
  36.     End With
  37.  
  38.     'import data
  39.  
  40.     Cells(2, 1).CopyFromRecordset rst
  41.  
  42.     'clean up references
  43.  
  44.     rst.Close
  45.     cnn.Close
  46.     Set rst = Nothing
  47.     Set cnn = Nothing
  48. End Sub
  49.  
I don't know why I'm not getting any records out. I know the SQL works. I tested it in MSQuery32 as well as Access and if I put the query results in its own table and run this code with the new table it returns records as well. I am well and truly stumped.
Jul 23 '07 #1
Share this Question
Share on Google+
1 Reply


P: 3
CJD
Can anyone help me with this.
Jul 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.