Avatar of rommelit
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?)
Visual Basic.NET

Avatar of undefined
Last Comment
rommelit

8/22/2022 - Mon
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
        MyConnection.Open()

        DA.Fill(Dt)

        MyConnection.Close()
        DataGridView1.DataSource = Dt

        Command = Nothing
        MyConnection = Nothing
        ConnectionString = Nothing
        DA = Nothing
rommelit

ASKER
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.
awilde2000

It depends on the size of the database.  How large is it?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
rommelit

ASKER
393 MB
awilde2000

That could be why
rommelit

ASKER
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
awilde2000

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
rommelit

ASKER
It looks like it is using the table adapter.  Where would the string be?
awilde2000

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
rommelit

ASKER
It does seem that the dataset is local.  Is this a limitation of VB2005 Express?  I thought that I had linked it.
awilde2000

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?
rommelit

ASKER
I used the wizard through the Database Explorer
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
awilde2000

Open up the app.config file in the solution explorer, this displays the connection string, Never knew it was there until now
rommelit

ASKER
It was there.  I have deleted the string and will try your suggestion.
ASKER CERTIFIED SOLUTION
awilde2000

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
rommelit

ASKER
there was another string in there.  Not sure why or how but there was.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23