Need help connecting to my Access DB using VB 6.0!!!

[shef015]

I am fairly new with VB even though I have used ASP and C++ before. My problem is with VB connecting me to my database i have set up. If i could get through this problem i am good to go, however i am stuck right now. Any help would be very much appreciated. Here is a portion of my code, this is the part that gives me the error. That part is in the first line with the New ADODB.Connection. It doesn't seem to like it and i have no clue why. Any comments of suggestions would be great, thanks.

Set conn = New ADODB.Connection
conn.ConnectionString = "Driver={Microsoft Access Driver};" & "DBQ=C:\TEMP\HHM\HHM.mdb"
conn.Open

Prodlist = "Select * From Products where '" & txtVC & "' =VenderCode"
Set rs = conn.Execute(Prodlist)

Do While rs.EOF
lstCatalog.AddItem rs!ProductID
rs.MoveNext
Loop

rs.Close
conn.Close

 

[Sypher2]

If the very first line is giving you the error, I would imagine you haven't set a reference to the Microsoft ActiveX Data Objects library.

Go to Project|References then scroll down the list to find the library.

Also, did you declare the variable somewhere?
Dim conn As ADODB.Connection

 

[zemp]

Sypher2 is correct. Make sure that your reference is good and that your connection variable is declared. Then you will need to change your connection string to the following

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\HHM\HHM.mdb;Persist Security Info=False"

Thanks and Good Luck!

zemp

 

[shef015]

Ok thanks very much for the help, i knew something wasn't setup in vb and i had no clue how to check. I knew ADODB library wasn't included in the vb but didn't know how to add it.

My next question is where would be a good place to open the database connection cause i have to continually work with an open connection within the application. Would it go in sub Form_Load or Sub Main, or somewhere else? how would i later call the conn variable in other procedures? Thanks again

 

[zemp]

Where you open your database connection and the scope of the connection is really up to you. I suggest that if your database is local and/or you don't have more than a couple of users then open a global connection when the app opens, keep it open so that you can use it whenever you need to and close it when the app closes.

A connection that is open will demand more resources. One connection may be negligable however. Opening and closing connections takes time. The amount time will depend on the number of users accessing the database and your network, if the database is on a server.

It is always a trade off. The decision is yours. Thanks and Good Luck!

zemp

 

[shef015]

well i am probably just going to keep it open then since it will be running on only 2 machines simultaniously and the db is on a server. i guess i will see how things are working with that and debate changing it. the app is going to update and modify the database repeatedly. if i got any more questions i will post them

 

[Sypher2]

I would open the connection in the Form_Load event.

Be sure to Dim the conn variable in the General Declarations section of the form module so it can be called or used in any event procedure in the module.

It's also good practice to close the connection in the Form_Unload or Form_QueryUnload event.

 

[msmish]

I actually have a question?
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.

 

[zemp]

See thread709-425096 Thanks and Good Luck!

zemp

 

[johnwm]

See also FAQ referred below regarding multiple postings!
________________________________________________________________
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.'