Link to home
Start Free TrialLog in
Avatar of rommelit

asked on

How can you link to an Access Database w/o having to import it into the application?

I am trying to write a relativly simple application using VB2005 Express Edition in which after a user is authenticated they pass on to a windows form with a data grid.  This data grid is based on a query from an access db.  I don't want to the user to have to import the db but rather just connect to it from the network.  What is the best way to do this?  (graye, can you help?)
Avatar of awilde2000

You don't need to import the database into VB just connect to it.

Delcare a connection object, open the connection, retrieve the data into a datatable and then close the connection

        Dim ConnectionString As String

        ConnectionString = "c:\MyDatabase.mdb"

        Dim MyConnection As New OleDb.OleDbConnection

        MyConnection.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
                "ocking Mode=1;Data Source=" & ConnectionString & ";Mode=Share Deny None;" & _
                "Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System data" & _
                "base=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet O" & _
                "LEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet O" & _
                "LEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;U" & _
                "ser ID=Admin;Jet OLEDB:Global Bulk Transactions=1"

        Dim Command As New OleDb.OleDbCommand

        Command.Connection = MyConnection
        Command.CommandText = "SELECT MyTable.MyColumn FROM MyTable;"

        Dim Dt As New DataTable

        Dim DA As New OleDb.OleDbDataAdapter

        DA.SelectCommand = Command


        DataGridView1.DataSource = Dt

        Command = Nothing
        MyConnection = Nothing
        ConnectionString = Nothing
        DA = Nothing
Avatar of rommelit


I had used similar code but when I went to deploy it took forever to install.  I assumed that it was copying the db to the client.
It depends on the size of the database.  How large is it?
393 MB
That could be why
But you said that it should just link to a db.  I am trying to have the application link to a network db.  Would I be better suited using a SQL?
Then you do not need to include you database in the solution explorer,  just set the connection to your network database and thats it.  You may want to do some error checking to ensure the user is coonected to the network and that he can access the database.

ConnectionString = "c:\MyDatabase.mdb"  ' point this to your network db
It looks like it is using the table adapter.  Where would the string be?
Look in the properties of the table adapter, this should tell you what connection it is using, then go to select that connection --> Propereties and modify the setting.
It does seem that the dataset is local.  Is this a limitation of VB2005 Express?  I thought that I had linked it.
It must be somewhere either through the code window or on the designer.  If you cannot see it in the code widow, go up to the left hand drop down list at the top and maybe you can select it there, once done select the right hand drop down list and see whats avaiable.

How did you connect to it in the first place?
I used the wizard through the Database Explorer
Open up the app.config file in the solution explorer, this displays the connection string, Never knew it was there until now
It was there.  I have deleted the string and will try your suggestion.
Avatar of awilde2000

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
there was another string in there.  Not sure why or how but there was.