asked on

ADODB Connection to a Microsoft Access DB from a VB 6 application

I am trying to set up my first connection using ADO from VB6 to a Access database.

Some settings:
I have the Microsoft ActiveX Data Objects Recordset 2.7 Library and the Microsoft ActiveX Data Objects 2.7 Library references both selected in VB6 for the application

The Access database is created and closed and the table names are correct

I am trying to open a connection to the database and then retrieve a simple record from the database.

Connection:

Global Conn As New ADODB.Connection
Public Sub DBConnect()
Dim Cmd As New ADODB.Command
Dim RS As ADODB.Recordset
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Program Files\Microsoft Visual Studio\VB98\Database Editor\DBEditor.mdb"
Conn.Open

Cmd.CommandText = "MT"
Cmd.CommandType = adCmdTable
Set RS = Cmd.Execute

Error:
The above code gives me an error RunTime Error: 3709 The connection can not be used to perfrom this operation. It is either closed or invalid in this context.

So I tried a new type of query using SQL and got the same error.

Global Conn As New ADODB.Connection
Public Sub DBConnect()
Dim Cmd As New ADODB.Command
Dim sqlString As String
Dim RS As ADODB.Recordset

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Program Files\Microsoft Visual Studio\VB98 \Database Editor\DBEditor.mdb"
Conn.Open
sqlString = "SELECT * FROM MT"
cmd.CommandText = sqlString
cmd.CommandType = adCmdText
Set RS = Cmd.Execute
End Sub

I get the same error message again. I think something is wrong with my database even though it connects with no error's HEEEEEEEEEEEEEEEEEEEEEEEEEEELP!!!!!!!!!!!!!!!!!!!!!!

Adam

Ryan Chong

try like:

Global Conn As New ADODB.Connection
Public Sub DBConnect()
Dim sqlString As String
Dim RS As ADODB.Recordset

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Program Files\Microsoft Visual Studio\VB98 \Database Editor\DBEditor.mdb"
Conn.Open

if Conn.State = 1 then 'opened
sqlString = "SELECT * FROM MT"
Set RS = Conn.Execute(sqlString)
'....
end if
End Sub

ASKER CERTIFIED SOLUTION

kaliyappan_k

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pi7

Or try this
Dim rs as New ADODB.Recordset
Dim Conn As New ADODB.Connection
Public Sub DBConnect()
With
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Program Files\Microsoft Visual Studio\VB98 \Database Editor\DBEditor.mdb"
.Open
End With
rs.Open "SELECT * FROM MT",Conn,adOpenDynamic,adLockOptimistic,adcmdText
End Sub

leonstryker

You do not need a reference to Microsoft ActiveX Data Objects Recordset 2.7 Library

You are not passing the connection string to your connection object. Look at some of the suggestions above, as to how to do it.

Leon