Member Avatar for Jollyyy100

I want to connect my access database to my project in vb 6.

What are the steps i should follow?

Once i'm done with that, i want to know how to drag them and place them on a form (like the way we do it in VB.NET).

Thank you

  • 6 Contributors
  • 13 Replies
  • 3K Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by millyn

Dani AI

Generated

You are right that a “connection string” approach in VB6 means ADO under the hood. Building on ’s note and ’s nudge about the missing connection, here is a minimal, reliable pattern you can drop into your project. First, add a reference to “Microsoft ActiveX Data Objects 2.x Library” (2.8 is fine). For .mdb files use the Jet provider; for .accdb use ACE (you may need the Access Database Engine installed). Note that Jet is 32-bit only, so your app must run 32-bit. Jet provider docs. Jet 32-bit only. If you are on .accdb, use the ACE provider (12.0/16.0) which comes with Office/Access or the Access Database Engine redistributable. (learn.microsoft.com)

Put this helper in a module and point it at your file next to the EXE:

Public Function OpenDb(ByVal dbPath As String) As ADODB.Connection
    Dim cn As New ADODB.Connection
    cn.CursorLocation = adUseClient
    If LCase$(Right$(dbPath, 6)) = ".accdb" Then
        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"
    Else
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
    End If
    Set OpenDb = cn
End Function

To save customer data safely (no string concatenation), use parameters with ADODB.Command, then verify the insert with SELECT @@IDENTITY (Jet/ACE return the last AutoNumber for the current connection). CreateParameter. Command object. Retrieving Access Autonumber with @@IDENTITY. (learn.microsoft.com)

Public Sub SaveCustomer(fn As String, ln As String, amt As Currency, paidOn As Date)
    Dim cn As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
    Set cn = OpenDb(App.Path & "\Database.mdb")
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = "INSERT INTO Customers (FirstName, LastName, AmountPaid, PaymentDate) VALUES (?,?,?,?)"
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, fn)
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, ln)
        .Parameters.Append .CreateParameter(, adCurrency, adParamInput, , amt)
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , paidOn)
        .Execute
    End With
    Set rs = cn.Execute("SELECT @@IDENTITY")
    Debug.Print "Saved. New CustomerID = " & rs.Fields(0).Value
End Sub

To “drag onto a form” like VB.NET, drop a DataGrid control on the form and, in Form_Load, open a recordset and bind it in one line: Set DataGrid1.DataSource = cn.Execute("SELECT CustomerID, FirstName, LastName FROM Customers"). This keeps UI simple while your saves use the safer command pattern above.

Recommended Answers

All 13 Replies

Member Avatar for AndreRet

You guys need to search a bit more please.:)

Have a look at This link that was discussed at the same time as your first post today.

The basics is all there.

Member Avatar for Jollyyy100

Gone through it, doesn't help.
But
Thank you

Member Avatar for AndreRet

Then we are missing the question. What exactly do you need. I'm off now, will reply tomorrow morning.:)

Member Avatar for abelingaw

What part are you trying to connect your database to?

Is it the project or a datagrid or listview?

If it is your project, the link sir Andre has given will show the basics.

Member Avatar for abu taher

search engine can help you. you get lot of answer of your question.

Member Avatar for Jollyyy100

Okay, i'm generally making a program which captures information about customers and payments.
To do so, i'm using vb6.
In order to store and retrieve this data i have made an access database.
Now i do not know how to connect that table created to my project.
But the major thing is that i do not want to use the ADO and stuff, i want to use a connection string.
So i wanted to ask, how do you do that. Are there a set of codes required for a connection string?
Do i have to make an interface with a label (firstname,lastname) and textboxes ....
And after we do that, how can we make sure it is saving in access.

Hope my question is clear enough now, i have tried my best.
Thank you

Member Avatar for AndreRet

ADO is active data objects, making use of connection strings etc. So, that said, have a look at the link I gave above. The code discussed there as well as the references needed is all in there.

Member Avatar for Jollyyy100

Ok...
Thank you

Member Avatar for abelingaw

I think your missing the connection part.

Here:

'Put this in General Declaration area

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

Then have this where you want to create the connection

Set db = New ADODB.Connection
        db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\Database.mdb"
    
'Change Database to your database name

Set rs = New ADODB.Recordset
        rs.Open "Select DATABASETABLENAME from TABLEFIELD", db, adOpenStatic, adLockOptimistic

'DATABASETABLENAME is the table from your database
'TABLEFIELD is where you records be found

Or you can just do your connection by creating a module.

Public DBLink As New ADODB.Connection
Public RecSet As New ADODB.Recordset

Public Sub Con(Database As String)

DBLink.Provider = "Microsoft Jet 4.0 OLE DB Provider"
DBLink.ConnectionString = "Data Source=" & App.Path & "\" & Database
DBLink.Open

'Still the Database is the database name - rename it  to your own

End Sub

Then just call the connection on where you need it.

E.g in a form:

Call Con
Member Avatar for Jollyyy100

I'll try it out...

Thank you

Member Avatar for AndreRet

It's a pleasure.:) If it helped solving your problem, please mark this as solved, thanks.:)

Member Avatar for jazz00030

pls

Member Avatar for millyn

i want to write codes that can connect each of the created entry forms in vb 6 to ms access tables. any help is welcome

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.