care to help a newbie with connecting vb6 to ms access?

[cherrydee]

I have these in my module:

Public con As New ADODB.Connection
Public rs1 As New ADODB.Recordset


Public Sub opendb()
Dim connect As String

connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\..\Documents\tracking system\tracking.mdb;Persist Security Info=False"
 
With con
    .Open connect
    .CursorLocation = adUseClient
 End With
End Sub

and in my log in form:

Set rs1 = con.Execute("Select * from useraccounts Where uname like '" & txtUserName & "' " & _
" And upassword Like '" & txtPassword & "'")

If (rs1.RecordCount > 0) Then
    ..
Else
      ...
 End If
End Sub

These are my old codes way back 2011 also using vb6 but my database was MySQL. Now I am using MS Access hence the jetoledb in provider. It gives me error saying "operation is not allowed when object is closed"

I tried adding these:
Dim con As New ADODB.Connection
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.ConnectionString = "Data Source=" & App.Path & "\tracking.mdb"
con.Open
with these, I can run the program without errors. But wouldn't it add conflict to the codes in module?
Thanks

PS: is it normal for my .mdb located in vb6 dir (v98) to become readonly?

[DataMiser]

You need to call your open db sub before you try to open a recordset on it.

Also you should not use the New keyword in your dim statement. While it will work it is not the best way to do it.

Much better to do it like I told you in the other thread. Dim the connection in the module then set to new connection in the first bit of code that runs, normally that would be the form load event of the startup form or if the case you have a sub main as your startup object then it could go there.


And yes it is normal for anything under the \program files tree to become read only. You should not store your code or data files there, this has been the case in every OS since XP.

Also using app.path can cause an issue as that is usually somewhere in the program files tree and thus the db would be read only and crash your program.