[Resolved] MS Access Database and (dynamic) Connection String

[Sgt Spike]

Hey all,
I'm working on creating an installation package for my program. It includes an MS Access database, and I've just been using an ODBC connection to connect to it. I was building the connection string on the basis that I had a defineable location for the database. Now I need the connection string to just look for the file in the same folder that the installation was made to (and also the same folder that the .exe and other files will be in).
Here is the connection string I'm using now:

Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=MS Access Database;DBQ=C:\Documents and Settings\JP\My Documents\Visual Studio Projects\BrickBlaster\levels.mdb;DefaultDir=C:\Documents and Settings\JP\My Documents\Visual Studio Projects\BrickBlaster;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

I guess what I'm asking is what do I change the DBQ= and DefaultDir= to?

[RobDog888]

Use the Application.StartupPath instead of hard coding the db path, assumes the db is located in the exe's local folder.

[Sgt Spike]

That's what I was looking for. I'm not getting it to work though... Here's the new connection string:

Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=MS Access Database;DBQ=Application.StartupPath\levels.mdb;DefaultDir=Application.StartupPath;DriverId=25;FIL=M S Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

When I click on "Test Connection" it gives me this error: "Test connection failed because of an error in initializing provider. [Microsoft][ODBC Driver Manager] Invalid connection string attribute."

BTW, I'm making this connection by right-clicking on Data Connections in Server Explorer and clicking "Add Connection..." Then I select the Provider tab and select "Microsoft OLE DB Provider for ODBC Drivers". And then I click next, and put in my connection string. Am I missing any steps, or should I be doing this completely differently?

[kleinma]

you should be using the "Microsoft Jet 4.0 OLE DB Provider" to make your connection. using ODBC requires you setup a datasource on the machine, you don't really want that. OLEDB is newer and more flexible than ODBC.

Change 4.0 to 3.1 if you happen to be using a real old Access DB like 97 or earlier.

[RobDog888]

You need to place the double quotes outside of the vb code so vb knows to evaluate the function as a function and not a string text.

.Connectionstring = "Provider=MSDASQL.1;Persist Security Info=False;DBQ=" Application.Startuppath & "...blah blah blah...."

[Sgt Spike]

Here's the code I'm using for the connection string now. I'm setting it in code rather than in the properties window, as it didn't seem to like that. It's still coming up with an error though.

OdbcConnection.ConnectionString = "PageTimeout=5;DSN=MS Access Database;DefaultDir=" & Application.StartupPath & ";DriverId=25;DBQ=" & Application.StartupPath & "levels.mdb;FIL=MS Access;MaxBufferSize=2048;UID=admin"
        Dim myCommand As New Odbc.OdbcCommand(getLineQuery, OdbcConnection)
        OdbcConnection.Open()

The error (which is caught and written to the console) is:

Error in buildBricks(): System.Data.Odbc.OdbcException: ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.
at System.Data.Odbc.OdbcConnection.Open()
at BrickBlaster.Form1.buildBricks() in C:\Documents and Settings\JP\My Documents\Visual Studio Projects\BrickBlaster\Form1.vb:line 443The program '[2900] BrickBlaster.exe' has exited with code 0 (0x0).

RobDog, Is there anything wrong with the connection string as I am writing it?

klienma, or anyone else, could you tell me a bit more about Jet 4.0 OLE DB Provider?

[kleinma]

well for starters, let me know what version of .NET you are using.

It doesn't have much effect on the connection string you need, but I can probably provide you a sample DB connection.

[Sgt Spike]

I'm using .NET 2003 Academic version.

A sample DB connection with an OLE DB connection would be great. I was having trouble with it when I was trying to do it myself.

[kleinma]

Here you go.

[Sgt Spike]

Just figured it out and got the ODBC connection working... in the connection string, instead of putting DBQ=" & Application.StartupPath & "levels.mdb, I just put DBQ=levels.mdb. That should fix my problem. Thanks for the help all.

[kleinma]

I still suggest you use OLEDB

[JAKSupport]

It works because the database is in the same path as your application. Is each one of your users getting a seperate database? I second Klienma's suggestion...I'd drop access and use SQL Server if I had the money .