Problem with VBA code trying to query and access database

[Brett Ellingson]

Hello,

I have been trying to get a query that is written in VBA to get data
from access and update it in excel. I can get the exact same lines of
code to run with one database and then on another I cannot get the
same lines of code to run.

I tried the sample code on the northwind.mdb that is included as a
sample with MS office, but when I modify it for a db on a network at
work I get an error: "Run-time error ‘2147217900(80040e14)':
Automation error.

I get the error on the line marked below with an asterisk at the
beginning. It seems like it makes a good connection with the database,
but the objects/methods etc that are being used to create and open the
recordset don't seem to work for some reason.

I've read, read and re-read as much documentation as I can find and I
was wondering if someone could point me in the right direction.

The other option I've considered is to export the data to excel using
transfer spreadsheet command or to also create an excel object in
Access and control excel that way. But I'd prefer to be able to
control everything from excel if possible. I say this because what I'm
trying to accomplish is to automate a series of excel reports that I
do every month. It takes about 3-4 days to do these reports, but with
automation it seems I can drop that to not even a days worth of work.

Here is the code; I put an asterisk by the line that is giving me the
error when I step through the code. The line the error occurs on is:
rst.Open

Also F.Y.I, the database I'm trying to query is a front end program
for a sql server whereas the query that is working is simply an access
mdb

Thanks for the help and let me know if you need more info.

Sub RetrieveAccessData()

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim Nsql As String, Njoin As String, Ncriteria As String
Dim NewBook As Workbook
Dim i As Integer

' Create the Connection object.
Set conn = New ADODB.Connection
With conn
' Set the OleDB provider for the connection.
.Provider = "Microsoft.JET.OLEDB.4.0"
' Open a connection to Northwind.mdb.
'.Open "C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb"
.Open "K:\AddOn Databases\ATMManagerAddOn.mdb"
End With

'***********SQL for ATMManagerAddOn.mdb"***************
Nsql = "SELECT ATM.TerminalID"
Njoin = "FROM ATM;"
Ncriteria = ""

'***********SQL for Northwind.mdb query*****************
'Nsql = "SELECT DISTINCTROW Categories.CategoryName,
Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice "
'Njoin = "FROM Categories INNER JOIN Products ON
Categories.CategoryID = Products.CategoryID "
'Ncriteria = "WHERE (((Agreement.Line)=50180))ORDER BY
ATM.DateDeinstalled;"

' Create a new Recordset Object.
Set rst = New ADODB.Recordset
With rst
' Connect this recordset to the previously opened connection.
.ActiveConnection = conn
' Retrieve all records from the Customer table.
****.Open Nsql & Njoin & Ncriteria, conn ', adOpenDynamic,
adLockBatchOptimistic
End With

' Add a new worksheet to this workbook
Set NewBook = Workbooks.Add
' Loop through all of the fields, returning the field names to the
worksheet.
For i = 0 To rst.Fields.Count - 1
NewBook.Sheets(1).Range("a1").Offset(0, i).Value =
rst.Fields(i).Name
Next i
' Copy the recordset to the new worksheet.
NewBook.Sheets(1).Range("a2").CopyFromRecordset rst

' Close the recordset.
Set rst = Nothing
' Close the Connection.
conn.Close
End Sub

 

[Duane Hookom]

It looks like you need a space after TerminalID:
Nsql = "SELECT ATM.TerminalID "
Njoin = "FROM ATM;"
Ncriteria = ""

 

[Brett Ellingson]

That was exactly what I needed...amazing how a space can make all the difference.

Thanks mucho!!