VB6 and Access Database

[mms_]

Hello,

Is it possible for VB6 to connect to a Microsoft Access ACCDB database?

[SamOscarBrown]

uh.....yes!

[SamOscarBrown]

Add a REFERENCE to Microsoft ActiveX Data Objects X.X Library

'Dim variables to use
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command


Public Sub dbConnect()  'connection to the DB

   Set cnn = New ADODB.Connection
      With cnn
       .Provider = "Microsoft.ACE.OLEDB.12.0"  'THIS is for ACCESS 2007
       .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\yourdatabasename.accdb;"
'        .Provider = "Microsoft.Jet.OLEDB.4.0"  'THIS is for ACCESS 2003
'       .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\yourdatabasename.mdb;"
       .CursorLocation = adUseClient
       .Open
      End With
End Sub

example of usage:

            Set cmd = New ADODB.Command            
            Set cmd.ActiveConnection = cnn
            cmd.CommandText = "Select fieldname from TableName"  'substitute your own query here
            Set rs = cmd.Execute()

[SamOscarBrown]

That is ONE way (many ways to use queries).

[Zvoni]

Bah.
Never used the Command-Object

Just do a

Set RS=New RecordSet
RS2.CursorLocation = adUseClient
RS2.Open SQLString, MyConnectionObject, , adLockReadOnly  'Or Whatever options you need

EDIT: If it's a local Database (as in: Program is used only on that computer) i'd even setup a DSN and be done with the hassle
Then it's just a

Set cnn=New Connection
cnn.Open "MyDSN", UID, PWD 'If necessary

[dilettante]

I wouldn't use a creaky old DSN. That is for ODBC, and if you force the use of a DSN then ADO has to use the clunky shim MSDASQL Provider. if necessry it will chuckle at you and quietly insert it as the Provider.

Besides the extra overhead, this also makes it necessary to use the really old Jet SQL-89 syntax instead of the SQL-92 syntax that applies when using the proper Jet/ACE Provider instead. It also prevents access to a lot of newer Jet/ACE features.

DSNs were replaced by UDLs a long, long time ago. Scary to see anyone bringing something as old and problematic as DSNs up today.

A UDL connection string is simply:

File Name=xxx

Where "xxx" is a relative or absolute path to a .UDL file.

[mms_]

Thanks for all replies

So using Sam's code:

Private Sub Command2_Click()

    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command

    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\DB6.31.accdb;"
        .CursorLocation = adUseClient
        .Open

        MsgBox cnn.State
        
        cnn.Close
    
    End With

End Sub

I am able to open an accdb (MsgBox returns 1)

Problem I can see before I continue, is that:
- "Run" test app
- click Command2 (successful because MsgBox returns 1)
- test app seems OK
- clos app (Form1) with "X" button (OK and back in IDE)
- click "Run" again - CRASH!

Edit:
A second "click" of Command2 before closing test app also results in CRASH

[SamOscarBrown]

I don't see your issue...works fine.

[mms_]

On my end anyway, code only executes on FIRST CommandButton Clicks.
Any subsequent Click will result in CRASH.

[DataMiser]

I don't really see anything wrong with the code there. Do you have more code that is not shown?

When it crashes what it the error message you get?

I do not like the idea of creating the connection object in the command button routine like that. It is fine if that is the only place you need to access the db but that is almost never the case when working with a db. I would set the connection object as a form level or possibly global level var and initialize it at start up, then open and close as needed.

[SamOscarBrown]

I usually create the connection object once...at form load...

[mms_]

That is the only code in my little test app (other Option Explicit setting at top of Form code)

No error msg
I click the command button, and MsgBox pops up and says "1"
The Form remains in an "idle" state as long as I want, but if I click command button again,
the blue spinning "hourglass" circle spins for a few seconds, then the form shuts down,
then the IDE shuts down, and I am left sitting in the folder where the app resides.

I'm thinking it might have something to do with my Project References perhaps?
Which should I have checked?

[Schmidt]

I'm thinking it might have something to do with my Project References perhaps?
Which should I have checked?

When I use ADO against MS-SQLServer, I usually go with 2.6 (but 2.5 works fine also).

I hope you're aware that using the AccDB-OleDriver requires installation on Win10...

So, is there a specific reason you *have* to use it?

In case you want to just have a small "App-DB", then "JET 4" (with normal *.mdb Files) should work fine -
or better: switch to SQLite (which the "rest of the world" is using for App-local storage).

Olaf

[SamOscarBrown]

I have been going with 2.8...but, hey, it seems to always work for me...

Again, I am not experiencing any of the issues you have....so, ANY other code in your app besides what you posted?

[mms_]

I hope you're aware that using the AccDB-OleDriver requires installation on Win10...

Can't say I was actually aware, but I am working on a Windows10 system

So, is there a specific reason you *have* to use it?

Just need some method to work with Access accdb database


This is a work thing.
All employees have Windows10 O/S
All departments must use Microsoft Word/Excel/Access (Office365)

I'm trying to work with data from other departments, and this data is stored in either Excel or Access

These are the References I have checked:
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
Microsoft Access 16.0 Object Library
Microsoft ActiveX Data Objects 6,1 Library
Microsoft DAO 3.6 Object Library
Microsoft OLE DB Service Component 1.0 Type Library
Microsoft OLE DB Simple Provider 1.5 Library

SamOscarBrown
No other code besides the Option Explicit statement

[SamOscarBrown]

So look....open the connection when your program starts (main form load)...and leave it at that...no button push or anything. Then create your queries as you see fit...been doing this for decades, no issue as you say you are experiencing. Been on Windows 10 since it arrived, and also have Windows 11 on one computer...same programs (using Access DB tables) work on both.

[DataMiser]

I have used 2.6 in everything for years now, I also use mdb format for any access databases I may use though in recent years I do not use Access dbs that much, more likely to use SQL Server or SQL Lite.

[Zvoni]

Why do you have ADO AND DAO referenced?

[ChrisE]

Thanks for all replies

So using Sam's code:

Private Sub Command2_Click()

    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command

    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\DB6.31.accdb;"
        .CursorLocation = adUseClient
        .Open

        MsgBox cnn.State
        
        cnn.Close
    
    End With

End Sub

I am able to open an accdb (MsgBox returns 1)

Problem I can see before I continue, is that:
- "Run" test app
- click Command2 (successful because MsgBox returns 1)
- test app seems OK
- clos app (Form1) with "X" button (OK and back in IDE)
- click "Run" again - CRASH!

Edit:
A second "click" of Command2 before closing test app also results in CRASH

you Open the connection and close connections somehow with diffrent Foms and Buttons, not really clear what.

you can check the state of ADO, here a sample

'.....
 If (adoCn.State & adStateClosed) Then
    Debug.Print "The con object is currently closed."
ElseIf (adoCn.State & adStateConnecting) Then
    Debug.Print "The con object is currently connecting."
ElseIf (adoCn.State & adStateExecuting) Then
    Debug.Print "The con object is currently executing."
ElseIf (adoCn.State & adStateFetching) Then
    Debug.Print "The con object is currently fetching."
ElseIf (adoCn.State & adStateOpen) Then
    Debug.Print "The con object is currently open."
End If
'.......

[Zvoni]

Private Sub Command2_Click()

    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command

    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\DB6.31.accdb;"
        .CursorLocation = adUseClient
        .Open

        MsgBox cnn.State
        
        cnn.Close
    
    End With

End Sub

You do realize, that your explicit "cnn.close" is within a With-Block?
Put the MsgBox and cnn.close after the End With (and maybe add a "Set cnn=Nothing" before exiting the sub)

[mms_]

Still no luck.

I've created a brand new test project from scratch,
and a brand new test database (Database1.accdb)
Database1 consists only of one Table (Table1) with an auto ID Field, and a field created by me (Table1),
having 2 records (1=Joe, 2=Fred)

The Project will execute properly ONCE only.

My COMPLETE VB6 project code (1Form1 Module, 0 Modules)
Form1 code:

Option Explicit


Private Sub Form_Load()
    Form1.Caption = "VB6/accdb"
End Sub


Private Sub Command1_Click()
    
    '-------------------------------------------------------------------------------------------
    ' Connect to data source
    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    cn.Provider = "Microsoft.ACE.OLEDB.16.0"
    cn.ConnectionString = "Data Source=" & App.Path & "\Database1.accdb;"
    cn.CursorLocation = adUseClient
    cn.Open
    '-------------------------------------------------------------------------------------------

    
    '-------------------------------------------------------------------------------------------
    ' Execute SQL queries etc.
    
    Dim strSQL As String
    strSQL = "SELECT Field1 " & _
             "FROM Table1"
             
    Dim rs As ADODB.Recordset
    Set rs = cn.Execute(strSQL)

    Do Until rs.EOF
        MsgBox rs.Fields.Item("Field1")
        rs.MoveNext
    Loop
    '-------------------------------------------------------------------------------------------
    
    
    '-------------------------------------------------------------------------------------------
    ' Clean-up
    
    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing
    '-------------------------------------------------------------------------------------------

End Sub

References are:
(Set by default)
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
(Set by me)
Microsoft ActiveX Data Objects 6.1 Library

My set-up is:
Windows 10 Pro Version 21H1 (64-bit operating system, x64-based processor)
Office 365
Word 32-bit
Excel 32-bit
Access (32-bit)

[Arnoutdv]

Have you tried this with Microsoft ActiveX Data Objects 2.6 Library (or 2.7 or 2.8) instead of 6.1?

[DataMiser]

I've never used the 6.1 reference. I always use 2.6. I started using this version many years ago and kept using it because it allowed the programs to work on both old and new systems and I have not needed the features added in later versions of ADO. Try using 2.6 instead of 6.1 and see if you get better results.

[mms_]

Just tried Microsoft ActiveX Data Objects 2.6 Library... same result.
(Also 2.7 and 2.8)

Tried making an .exe and testing.
Works properly!

So something when in IDE, does not go out-of-scope, or release when it should?

[DataMiser]

No idea what you are runnign into here. I have been working with ADO in VB6 for over 20 years and have never had any such issue.
Do you have service pack 6 installed for VB6?

[mms_]

Don't know
How do I check?

[wqweto]

Don't know
How do I check?

In Help->About search for some graphics that say "SP6" in plain text

cheers,
</wqw>

[mms_]

OK Thanks

It appears that yes.
Microsoft Visual basic 6.0 (SP6)

[Zvoni]

Any IDE-AddIns running?

[mms_]

No Add-Ins

[mms_]

I copied the problem test project to USB stick and tried on my home set-up;
there it works flawlessly.

Home set-up has:
Microsoft Office 365
Visual Basic 6.0 (SP6)
Windows 7 Professional (Service Pack 1) (64-bit)

Any more ideas as to why it does not work on my work computer?

[HiddenX]

Same problem here.

An ADO connection with
Provider = "Microsoft.ACE.OLEDB.12.0"

crashes the IDE on my work computer at the first command after the connection was successfully(!) opened.

If I compile to an .exe file everything works fine.

I'm working with Office/Access 2016 (32 Bit)
Access Database Engine (32 Bit)
VB6 SP6

When I convert the .accdb file to a .mdb and use Microsoft.Jet.OLEDB.4.0 as provider everything works again (In IDE and .exe)

[k_zeon]

Same problem here.

An ADO connection with
Provider = "Microsoft.ACE.OLEDB.12.0"

crashes the IDE on my work computer at the first command after the connection was successfully(!) opened.

If I compile to an .exe file everything works fine.

I'm working with Office/Access 2016 (32 Bit)
Access Database Engine (32 Bit)
VB6 SP6

When I convert the .accdb file to a .mdb and use Microsoft.Jet.OLEDB.4.0 as provider everything works again (In IDE and .exe)

running exe as a standard user, it see's the office installation ie access.
when running in IDE i assume its as Admin. if so then you would need to have office installed under the admin account as well.

I have a similar issue that i cannot debug in ide under admin account.

just my thoughts. Looking back over post , i see you conneted so disregard this post. sorry

[HiddenX]

running exe as a standard user, it see's the office installation ie access.
when running in IDE i assume its as Admin. if so then you would need to have office installed under the admin account as well.

I have a similar issue that i cannot debug in ide under admin account.

just my thoughts. Looking back over post , i see you conneted so disregard this post. sorry

The IDE is running with elevated rights. But the Windows-user is the same in my case.

[ercolesptr]

I have been struggling to make Vb6 work with an access 16 database which had tables mapped into a sharepoint site. When trying to access the tables would get an incompatible isim error even if I had the full MS access installed. After some experiments I found that the culprit was the DBEngine since Vb6 is old the engine included with the dataset control do not support access beyond 2003. The work around is here:

Dim wrkDefault As Workspace
Dim dbDatabase As Database
Dim strDBPath As String
Dim DAO120DBEngine As DBEngine

' Construct database path using App.Path
strDBPath = App.Path & "\Data.mdb"


Set DAO120DBEngine = CreateObject("DAO.DBEngine.120")
' Create a default workspace
Set wrkDefault = DAO120DBEngine.Workspaces(0)

' Open the database
Set dbDatabase = wrkDefault.OpenDatabase(strDBPath, False, False, "") ' No password

' Database is now connected and ready for operations
MsgBox "Successfully connected to database!", vbInformation

' Example of executing a query
Dim rstRecordset As Recordset
Set rstRecordset = dbDatabase.OpenRecordset("SELECT * FROM Cases", dbOpenDynaset)

' Process recordset as needed
While Not rstRecordset.EOF
' Your code to process records
Debug.Print rstRecordset!Name ' Example of printing a field
rstRecordset.MoveNext
Wend

' Clean up
rstRecordset.Close
dbDatabase.Close

I also found that if I set the recordset into a normal VB6 data control it works as usual, important not to close the recordset and database though:

Set Data1.Recordset = rstRecordset
Data1.Refresh

[ercolesptr]

Also for those looking to use ADO instead of DAO and using accdb format:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String

' Connection string for Access database
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\Data.accdb;"

Set conn = New ADODB.Connection
conn.Open strConn

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Cases", conn

' Process records
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF
Debug.Print rs!Name
rs.MoveNext
Loop
End If

' Clean up
rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing