I'm trying to use this Recordset method to check data in an Excel spreadsheet and update to an Access table, but am having problems:
'rsRecordset.Find(criteria,skiprows,direction,start)
I got a compile error right away the "Dim dbmain As ADOB.Connection, so I haven't been able to get further. Probably lots more to debug.
One other important thing is how to say "IF rsRecordset.Find NotFound Then... (how to catch when the Find fails).
Here's my subroutine. It's trying to find every record that has a value entered for the UDR column, then gets a "KEY" number to try to "Find" the corresponding "Key" record in the Access recordset--then update that field.
No doubt there are better ways...but I thought this approach should work. Any help is appreiciated! Note some of the variables are declared elsewhere as Public.
Private Sub UpdateAccessUDR()
'
Dim dbMain As ADODB.Connection
Dim AccTable 'The variable for Access
'Dim dbMain As ADODB.Connection 'gave compile error: type not defined
Dim strSQL As String 'define SQL string
Dim rsRecordset As ADODB.Recordset
Dim UDR 'variables for that field
Dim i As Integer
Dim TotRecNum As Integer
FilenameTest = "Test.mdb"
DocPath = "D:\Data\"
FnameTemp = DocPath & FilenameTest
AccTable = "tblCourses"
'open connection
'METHOD USING FnameTemp FOR FILENAME...should work
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FnameTemp & ";Persist Security Info=False"
' should select all records from the table
strSQL = "SELECT * FROM " & AccTable
'declare, set, open recordset
Set rsRecordset = New ADODB.Recordset
rsRecordset.Open strSQL, dbMain, adOpenDynamic, adLockOptimistic
'check for empty recordset
If rsRecordset.BOF And rsRecordset.EOF Then
'MsgBox "There is no record for this Key! Please run the ""CRS INPUT TO ACCESS"" macro first, then try again"
Exit Sub
End If
'---------------------------------------
Range("UDR").Select 'goes to first row of Excel data for UDR column
LastCellsWithData
' A routine that finds the last row and column numbers with data in it, sets to public variables lastrowwithdata and lastcolwithdata. Works!
TotRecNum = lastrowwithdata - 5 'calculates correct number of total records
'START THE LOOPING SECTION
For i = 1 To TotRecNum
UDR = ActiveCell.Value 'sets UDR to current cell value
If UDR = "" Then 'skip if empty
GoTo MOVEDOWN 'goes to movedown label
Else
KeyNum = ActiveCell.Offset(0, 1).Value 'gets Key number
rsRecordset.MoveFirst 'moves to first record 'need this before use the Find command
rsRecordset.Find ("Key=" & KeyNum) 'Should be able to find the record
'that matches that value in the Access table for Key field
'NEED THIS ROUTINE:
' IF rsRecordset.Find NotFound Then
' MsgBox ("There is no record for this Key! Please run the ""CRS INPUT TO ACCESS"" macro first, then try again")
' Exit Sub
' End If
rsRecordset("UDR") = UDR 'updates the UDR value for the current record
rsRecordset.Update 'neccessary every time you add/modify records
End If
MOVEDOWN:
ActiveCell.Offset(1, 0).Select
Next
Range("UDR").Select 'goes back to top for range name UDR
rsRecordset.Close 'closes the recordset....needed when done using that table
dbMain.Close 'This closes the dbase connection, should be done whenever done connecting
End Sub
'rsRecordset.Find(criteria,skiprows,direction,start)
I got a compile error right away the "Dim dbmain As ADOB.Connection, so I haven't been able to get further. Probably lots more to debug.
One other important thing is how to say "IF rsRecordset.Find NotFound Then... (how to catch when the Find fails).
Here's my subroutine. It's trying to find every record that has a value entered for the UDR column, then gets a "KEY" number to try to "Find" the corresponding "Key" record in the Access recordset--then update that field.
No doubt there are better ways...but I thought this approach should work. Any help is appreiciated! Note some of the variables are declared elsewhere as Public.
Private Sub UpdateAccessUDR()
'
Dim dbMain As ADODB.Connection
Dim AccTable 'The variable for Access
'Dim dbMain As ADODB.Connection 'gave compile error: type not defined
Dim strSQL As String 'define SQL string
Dim rsRecordset As ADODB.Recordset
Dim UDR 'variables for that field
Dim i As Integer
Dim TotRecNum As Integer
FilenameTest = "Test.mdb"
DocPath = "D:\Data\"
FnameTemp = DocPath & FilenameTest
AccTable = "tblCourses"
'open connection
'METHOD USING FnameTemp FOR FILENAME...should work
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FnameTemp & ";Persist Security Info=False"
' should select all records from the table
strSQL = "SELECT * FROM " & AccTable
'declare, set, open recordset
Set rsRecordset = New ADODB.Recordset
rsRecordset.Open strSQL, dbMain, adOpenDynamic, adLockOptimistic
'check for empty recordset
If rsRecordset.BOF And rsRecordset.EOF Then
'MsgBox "There is no record for this Key! Please run the ""CRS INPUT TO ACCESS"" macro first, then try again"
Exit Sub
End If
'---------------------------------------
Range("UDR").Select 'goes to first row of Excel data for UDR column
LastCellsWithData
' A routine that finds the last row and column numbers with data in it, sets to public variables lastrowwithdata and lastcolwithdata. Works!
TotRecNum = lastrowwithdata - 5 'calculates correct number of total records
'START THE LOOPING SECTION
For i = 1 To TotRecNum
UDR = ActiveCell.Value 'sets UDR to current cell value
If UDR = "" Then 'skip if empty
GoTo MOVEDOWN 'goes to movedown label
Else
KeyNum = ActiveCell.Offset(0, 1).Value 'gets Key number
rsRecordset.MoveFirst 'moves to first record 'need this before use the Find command
rsRecordset.Find ("Key=" & KeyNum) 'Should be able to find the record
'that matches that value in the Access table for Key field
'NEED THIS ROUTINE:
' IF rsRecordset.Find NotFound Then
' MsgBox ("There is no record for this Key! Please run the ""CRS INPUT TO ACCESS"" macro first, then try again")
' Exit Sub
' End If
rsRecordset("UDR") = UDR 'updates the UDR value for the current record
rsRecordset.Update 'neccessary every time you add/modify records
End If
MOVEDOWN:
ActiveCell.Offset(1, 0).Select
Next
Range("UDR").Select 'goes back to top for range name UDR
rsRecordset.Close 'closes the recordset....needed when done using that table
dbMain.Close 'This closes the dbase connection, should be done whenever done connecting
End Sub