ibgreat
07-31-2008, 01:34 PM
I am new to VBA, but have been following a text. I am trying to set up an unbound form. I did the example in my text without a problem. When I try to apply it to the db I am working on the recordset does not appear to load. When I open the form I get an error msg stating, "Invalid sql statement..." I tried to switch the code to a formal sql statment:
.open "SELECT * FROM tblPerson-D", cnABCdb
but that didn't work either. I tried posting in another forum to no avail. I thought I would go to the experts...PLEASE HELP, it's been two days. Here is the code:
Option Compare Database
Option Explicit
Dim rsPerson As ADODB.Recordset
Dim cnABCdb As ADODB.Connection
Dim strConnection As String
Dim blnAddMode As Boolean
Private Sub Form_Load()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\BehaviorDatabaseWorking.mdb;"
'create a new connection instance and open it using the connection string
Set cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection
'create a new instance of a recordset
Set rsPerson = New ADODB.Recordset
'set various properties of the recordset
With rsPerson
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tblPerson-D table using the existing connection
.Open "tblPerson-D", cnABCdb
'disconnect the recordset
.ActiveConnection = Nothing
End With
.open "SELECT * FROM tblPerson-D", cnABCdb
but that didn't work either. I tried posting in another forum to no avail. I thought I would go to the experts...PLEASE HELP, it's been two days. Here is the code:
Option Compare Database
Option Explicit
Dim rsPerson As ADODB.Recordset
Dim cnABCdb As ADODB.Connection
Dim strConnection As String
Dim blnAddMode As Boolean
Private Sub Form_Load()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\BehaviorDatabaseWorking.mdb;"
'create a new connection instance and open it using the connection string
Set cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection
'create a new instance of a recordset
Set rsPerson = New ADODB.Recordset
'set various properties of the recordset
With rsPerson
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tblPerson-D table using the existing connection
.Open "tblPerson-D", cnABCdb
'disconnect the recordset
.ActiveConnection = Nothing
End With