Using Recordset.Find to update Access Table from Excel

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Whoops...Here's what the start of the code was:

Private Sub UpdateAccessUDR()
'
Dim AccTable 'The variable for Access
Dim dbMain As New ADODB.Connection

This gave a compile error: user-defined type not defined
 
Upvote 0
To use:

Code:
Dim dbMain As New ADODB.Connection

I think you need to add a reference to the ADO object library - "Microsoft ActiveX Data Objects x.x Object Library".

If you haven't you can use late binding:

Code:
Dim dbMain As Object
Set dbMain = CreateObject("ADODB.Connection")
 
Upvote 0
Hi, if someone can help me i apreciate

I need to do exactly this, but my database is an excel file... i trying replace de name of filename but dont work.

thanks at all
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top