How to connect to a Access DB from a VB App (Novice) 1

[msmish]

I am new to VB and Access
I am running VB 6.0 and Access 2000
Could anyone tell me how I create the connection in VB to my access database.

Would either of the open statements below work
Public conn As ADODB.Connection
Public rs As ADODB.Recordset
Public shape As Integer
Public questionNum As Long

Set conn = New ADODB.Connection
conn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\currentwork\mda.mdb;" & _
"Uid=admin;" & _
"Pwd="


conn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & App.Path & "\mda.mdb;" & _
"Uid=admin;" & _
"Pwd="


If not, could you please provide me with the correct code to create the connection as well as the correct references I need in VB.

This will be a very small app that provides a question and aswer session. It will be installed on 2 or 3 individual machines.

 

[Capgard]

Here's what I use for my connection string...

Provider=Microsoft.Jet.OLEDB.4.0;
USER ID=xxxx;PASSWORD=xxxxx;
Data Source=C:\dbname.mdb;
Jet OLEDB:System database=C:\dbname.mdw;
Jet OLEDBatabase Password=zzzzzzzz

Your will differ if you don't have a database password and/or different userid/password other than Admin.

 

[zemp]

Here are a couple of connection string that I have used. These are for ADO 2.6 connection to an Access 2000 database.

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MyDatabase.mdb;Persist Security Info=False"

In the first the database must be located in the same folder as the VB application.

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";Persist Security Info=False"

In the second the variable strDBPath is used to determine the location of the database. I use this to allow the user to place the database anywhere. When the app is first run I use a common dialog control to allow the user to browse for the database and then save that path to the registry using the savesetting function. Each time the app is run after that I use the getsetting function to retrieve the saved path from the registry and use it to build the connection string. This way the user only has to browse the first time and if the database is moved.

Conn.ConnectionString = "DSN=myODBC"

The third one uses an existing system DSN created through the windows ODBC administrator. It is the simplest but caries extra overhead withe the addition of the ODBC drivers. This will work best for small one user app or if the database is local.

Thanks and Good Luck!

zemp

 

[msmish]

This is the code that I put in my VB module for my app based on forum suggestions, however I get a compile error when I try to run the program. It says INVALID OUTSIDE PROCEDURE.

Could you tell me what I am doing wrong... the code follows

Public conn As ADODB.Connection
Public rs As ADODB.Recordset
Public shape As Integer
Public questionNum As Long


Set conn = New ADODB.Connection
conn.ConnectionString = "DSN=mdx"
'conn.ConnectionString = "Driver={Microsoft Access Driver};" & "DBQ=C:\MDExpert\mda.mdb"
conn.Open


mdx is the name of the System DSN I setup with the ODBC Administrator. When I get the error the word Set become highlighted.

 

[CCLINT]

The second part of your code starting with "Set conn..." needs to be in a proceedure, such as "Sub Main" or "Form_Load", and not in the declarations section. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[johnwm]

To help you with what the various bits (modules, procedures, functions, subs etc) do, and where they are used, check out VBHelp

Look under: using Visual Basic/Programmers guide/Part 1
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[msmish]

Okay, adding the sub main got rid of the first compile error. But now I'm getting this error
"User Defined type not defined"
it is highlighting the following line.
conn As ADODB.Connection

Could someone provide a link to the guide that JOHMWM refers to in his reply.

Thanks

 

[CCLINT]

>"User Defined type not defined"
Have you added the "Microsoft ActiveX Data Objects Library" to your project under PROJECT|REFERENCES ? [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[CCLINT]

Oh, add a new form using the data form wizard and let is create for you the needed code for a connection (select "ADO-Code" for the connection type).
Then you can see an example of how to do this. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[JerryKlmns]

Hello everybody,

Here is a nice site for connection strings. Keep it handy!