Wondering why the following ADO connection to Access isn't working...

[troy]

Dim MYdb As ADODB.Connection
Dim MYrs As ADODB.Recordset

MYdb.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents
and Settings\tsigwin\Desktop\Work Log .mdb;USER ID=admin;Password=;"
MYrs.Open "Select * from Work Log.Work Log", MYdb, adOpenDynamic,
adLockOptimistic



Any ideas?

 

[Tim Williams]

Any error message?

There is a space in your file path just before ".mdb"

Tim

 

[Guest]

When does it fail and what error message are you getting?
Checking the obvious first, are you sure you want the space before the
period in \Work Log .mdb? And I assume that before you use your ADO
variables you are 'Set'ing them (e.g. Set MYdb as New ADODB.Connection)?

Some general troubleshooting guidelines I use with ADO:
- Put breaks or messageboxes in the code at critical points: here that would
be just after making the connection and just after returning the recordset.
Use the Immediate pane and/or locals window to see what is going on with your
ADO objects. Try manually typing in the Open commands, changing some of the
parameters.

- Make sure you have the connection string correct. One trick I use
sometimes is to use MSQuery to set up a simple external data query off the
same DB and then look at the connection string from that (which I can see by
the querytable.connection property).

Sorry can't be of more assistance without more info.

 

[troy]

The space is supposed to be there, its not my database. It give a
run-time error...automation error while trying to open the recordset.

 

[Guest]

Automation error is a little strange when working with ADO. Seems to imply
the problem is not in the ADO commands themselves but in the "server" app,
which in this case would be Jet. Are you sure you are specifying the correct
version of Jet? Do you have other ADO code that is successfully able to
access this database, or any Access database on your machine?

I would suggest checking the connection string as I suggested. Have MSQuery
do the grunt work for you. If you can connect via Query, the settings you
see should be the ones to use; if even Query has trouble connecting then I
think you might need to dig into your Office setup.

Sorry again I can't be of more help; I have some experience with ADO and the
typical errors I get trying to connect but have not run across an automation
error as the problem.

 

[Myrna Larson]

Did you show all of your code? If so, you are not instantiating the variables.

Dim MYdb As ADODB.Connection
Dim MYrs As ADODB.Recordset

Set MYdb = New ADODB.Connection
Set MYrs = New ADODB.RecordSet

MYdb.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents
and Settings\tsigwin\Desktop\Work Log .mdb;USER ID=admin;Password=;"

MYrs.Open "Select * from Work Log.Work Log", MYdb, adOpenDynamic,
adLockOptimistic

 

[Jamie Collins]

I would suggest checking the connection string as I suggested. Have MSQuery
do the grunt work for you. If you can connect via Query, the settings you
see should be the ones to use

Doesn't this give you an ODBC connection string? ADO is all about
OLEDB. While you can use the OLE DB provider for ODBC, Microsoft
considers it a depreciated component
(http://msdn.microsoft.com/library/d...us/mdacsdk/htm/mdac_deprecated_components.asp)
so it's best to avoid using it if possible.

The OP is using an OLE DB connection string already. I don't think it
is good advice to suggest changing it to ODBC. If you need a GUI tool
to build an OLE DB connection string for you, try this code (just four
lines, of course):

Sub ShowConnString()
Dim oDLink As Object
Set oDLink = CreateObject("DataLinks")
On Error Resume Next
MsgBox oDLink.PromptNew
End Sub

Then again, you may be able to generate an OLE DB connection string via
the MSQuery designer (I can only do it by using VBA). If so, please
post details here.

Jamie.

--