Syntax for Connecting to different Access db. 1

[IamCornholio]

I can not seem to find the correct syntax to connect to another db in vba. I want to run a query in another db and instead of:

set db = currentdb() HOWEVER!!!!

I can't seem to find the syntax for connecting to a different db on my server.

any ideas?

 

[Remou]

As far as I recall:

Set DB=Opendatabase ("C:\Data\Tek-Tips.mdb")

 

[IamCornholio]

Thanks Remou, I couldn't even find that in the library...I'll give a try. If it works, I'll remember you at Christmas...

 

[IamCornholio]

Um..... I need to pass it a password.... syntax is:
opendatabase("\\MYUNCPATH",,,[CONNECT])

I'm finding that I should put "Database Type;pwd=mypassword" for [CONNECT] and that connecting to JET type db's is a default and thereby optional leaving me with just the pwd=mypassword. It keeps telling me invalid pw tho....

Am I missing Something?

 

[Remou]

That looks like ADO, do you mean:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")

'[URL unfurl="true"]http://www.connectionstrings.com/[/URL]
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;"

cn.Open strCon
<...>

 

[IamCornholio]

nope. Its deffinitley DAO. I found information in the help topics about the CONNECT method, but can't make it work.

 

[Remou]

When you say password, do you mean a password for the database set by Tools->Security->Set Database Password? If so, you need to provide the options:

http://support.microsoft.com/kb/209953

 

[IamCornholio]

YUP. I thought you could read minds.... It turns out my UNC path is mucked up. Not sure what yet, but deffinitley the UNC path. I tried it with the Northwind db and it worked.

 

[IamCornholio]

It was my UNC path. I had a little tiny typo. I retyped it and it worked. Thanks for the help anyway... I appreciate it as always.