Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA code trying to query and access database
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA code trying to query and access database
It looks like you need a space after TerminalID:
Nsql = "SELECT ATM.TerminalID " Njoin = "FROM ATM;" Ncriteria = "" -- Duane Hookom MS Access MVP -- "Brett Ellingson" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA code trying to query and access database
That was exactly what I needed...amazing how a space can make all the difference.
Thanks mucho!! "Duane Hookom" wrote in message ... It looks like you need a space after TerminalID: Nsql = "SELECT ATM.TerminalID " Njoin = "FROM ATM;" Ncriteria = "" -- Duane Hookom MS Access MVP -- "Brett Ellingson" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel query to access database | Excel Discussion (Misc queries) | |||
Query of an Access database won't let me get more than 95 fields. | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Problem Code: Retrieving Stored Access 03 Query | Excel Discussion (Misc queries) | |||
How to do an Access Database Query in Excel | Excel Programming |