ADO connection question

[mcirvine]

Hi all,

I the below code example how do i need to program it so i dont need to hard code the path to the data source? I have the data source defined in my administrator tools but i dont know how i need to do it so i can point only to cost_accounting.mdb in vba.

Thanks

' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\CAS\cost_accounting.mdb;"

 

[nateobot]

Do you mean that you have a DSN connection that you want to use, rather than your current method?

If so then "DSN=myDsn;Uid=username;Pwd=;" should work.

A good site for connection strings is http://www.connectionstrings.com

 

[mcirvine]

Thats what i meant . Sorry still new to vb and all. Dont like it that the path is hard coded in my program.

Thanks nateobot

 

[Mile-O]

Then store the path in a table and refer to that when doing the ADODB connection.

 

[nateobot]

Either the path will be hardcoded in your application, or you will need to go to every PC that is using this and create the DSN entry. Also anytime the database you are linking to's location moves you will have to change the DSN entry on each PC vs. making only the change in your application.

Most people like the hardcoding method better especially when linking to an Access database.

I usually create a global variable and store the db path & name in there and then reference that for my connections. Or I create a DatabaseConnection sub that does my connection.

Just something to think about.

 

[colette]

test for global variable=nothing

Hi,
I have been trying to use a global variable that stores my connection string and as I move around the db, it sometimes loses the value and appears to be set to nothing (especially in development). I have yet to figure out how to trap for gblConn=Nothing so that I can reset it to the connection string. I use the variable for pass-through queries to Oracle to retrieve the next sequence number (auto-number) when I want to add a record to a table.

Do you have any suggestions on how to trap for ...
1) Lost connection to linked tables.
2) gblConn variables not set (=nothing). I have one that is for pass-through's and one that is to the linked tables basically.
3) Trapping for "ODBC Connection Failed", which sometimes seems to cause a "Type Mismatch" Error.

Colette