|
Help with using ADO to connect Excel to Access
Hi,
I am new to ADO and am having problems getting a simple example to work. When the followig code is executed the 'rs.Orders.Open StrSQL, cn' command throws 'Object recquired' exception. Neither strSQL or cn are null, can anybody help?? Sub test() Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.Open "File Name=\\lonfs01\users\ayleta\my documents\TGW Trade Query\test.udl" 'Create the SQL statement that does all the work strSQL = "SHAPE {SELECT TradeID, Product FROM TradesDone)" 'Create the recordset for the orders table rsOrders.Open strSQL, cn Do While Not rsOrders.EOF 'Print out the header rows, one at a time Debug.Print rsOrders!TradeID, rsOrders!Product, rsOrders.MoveNext Loop End Sub UDL String: Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties="DSN=MS Access Database;DBQ=\\lonfs2\users\ayleta\My Documents\TGW Trade Query\TGWTrades.mdb;DefaultDir=\\lonfs2\users\ayle ta\My Documents\TGW Trade Query;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin; " Excel 2000 Access 2000 Many thanks Alan |
Help with using ADO to connect Excel to Access
What's in the udl file?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Hi, I am new to ADO and am having problems getting a simple example to work. When the followig code is executed the 'rs.Orders.Open StrSQL, cn' command throws 'Object recquired' exception. Neither strSQL or cn are null, can anybody help?? Sub test() Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.Open "File Name=\\lonfs01\users\ayleta\my documents\TGW Trade Query\test.udl" 'Create the SQL statement that does all the work strSQL = "SHAPE {SELECT TradeID, Product FROM TradesDone)" 'Create the recordset for the orders table rsOrders.Open strSQL, cn Do While Not rsOrders.EOF 'Print out the header rows, one at a time Debug.Print rsOrders!TradeID, rsOrders!Product, rsOrders.MoveNext Loop End Sub UDL String: Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties="DSN=MS Access Database;DBQ=\\lonfs2\users\ayleta\My Documents\TGW Trade Query\TGWTrades.mdb;DefaultDir=\\lonfs2\users\ayle ta\My Documents\TGW Trade Query;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin; " Excel 2000 Access 2000 Many thanks Alan |
Help with using ADO to connect Excel to Access
strSQL = "SHAPE {SELECT TradeID, Product FROM TradesDone)"
Check your brackets! MH wrote in message oups.com... Hi, I am new to ADO and am having problems getting a simple example to work. When the followig code is executed the 'rs.Orders.Open StrSQL, cn' command throws 'Object recquired' exception. Neither strSQL or cn are null, can anybody help?? Sub test() Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.Open "File Name=\\lonfs01\users\ayleta\my documents\TGW Trade Query\test.udl" 'Create the SQL statement that does all the work strSQL = "SHAPE {SELECT TradeID, Product FROM TradesDone)" 'Create the recordset for the orders table rsOrders.Open strSQL, cn Do While Not rsOrders.EOF 'Print out the header rows, one at a time Debug.Print rsOrders!TradeID, rsOrders!Product, rsOrders.MoveNext Loop End Sub UDL String: Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties="DSN=MS Access Database;DBQ=\\lonfs2\users\ayleta\My Documents\TGW Trade Query\TGWTrades.mdb;DefaultDir=\\lonfs2\users\ayle ta\My Documents\TGW Trade Query;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin; " Excel 2000 Access 2000 Many thanks Alan |
Help with using ADO to connect Excel to Access
Good spot, but the brackets make no difference.
Any more ideas? MH wrote: strSQL = "SHAPE {SELECT TradeID, Product FROM TradesDone)" Check your brackets! MH wrote in message oups.com... Hi, I am new to ADO and am having problems getting a simple example to work. When the followig code is executed the 'rs.Orders.Open StrSQL, cn' command throws 'Object recquired' exception. Neither strSQL or cn are null, can anybody help?? Sub test() Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.Open "File Name=\\lonfs01\users\ayleta\my documents\TGW Trade Query\test.udl" 'Create the SQL statement that does all the work strSQL = "SHAPE {SELECT TradeID, Product FROM TradesDone)" 'Create the recordset for the orders table rsOrders.Open strSQL, cn Do While Not rsOrders.EOF 'Print out the header rows, one at a time Debug.Print rsOrders!TradeID, rsOrders!Product, rsOrders.MoveNext Loop End Sub UDL String: Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties="DSN=MS Access Database;DBQ=\\lonfs2\users\ayleta\My Documents\TGW Trade Query\TGWTrades.mdb;DefaultDir=\\lonfs2\users\ayle ta\My Documents\TGW Trade Query;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin; " Excel 2000 Access 2000 Many thanks Alan |
Help with using ADO to connect Excel to Access
The contents of the UDL file are detailed in the original post labeled
UDL String. Bob Phillips wrote: What's in the udl file? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Hi, I am new to ADO and am having problems getting a simple example to work. When the followig code is executed the 'rs.Orders.Open StrSQL, cn' command throws 'Object recquired' exception. Neither strSQL or cn are null, can anybody help?? Sub test() Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.Open "File Name=\\lonfs01\users\ayleta\my documents\TGW Trade Query\test.udl" 'Create the SQL statement that does all the work strSQL = "SHAPE {SELECT TradeID, Product FROM TradesDone)" 'Create the recordset for the orders table rsOrders.Open strSQL, cn Do While Not rsOrders.EOF 'Print out the header rows, one at a time Debug.Print rsOrders!TradeID, rsOrders!Product, rsOrders.MoveNext Loop End Sub UDL String: Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties="DSN=MS Access Database;DBQ=\\lonfs2\users\ayleta\My Documents\TGW Trade Query\TGWTrades.mdb;DefaultDir=\\lonfs2\users\ayle ta\My Documents\TGW Trade Query;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin; " Excel 2000 Access 2000 Many thanks Alan |
| All times are GMT +1. The time now is 02:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com