updating access table using excel vba

[SQUIDD]

Good morning

So I am using excel to add data to access database using forms. Got this working just great.

But, i need to be able to bring data back from access into my excel forms so i can modify and update.

The code below brings back data into a sheet for now, but its not the data i need, it seems its only the smallest id.
I just dont know how to write the code so it can search for a specific record.
I want to search a column called PO NUMBER in the access file. have added an input box for testing to search the record.

Sorry if this is obvious but i am extremly new to using access.

thanks in advance

dave

Sub today()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=D:\DATA\data.accdb;"
Set rs = New ADODB.Recordset
rs.Open "MASTER", cn, adOpenKeyset, adLockOptimistic, adCmdTable

PO = InputBox("ENTER RECORD TO FIND")

       Range("a1") = rs.Fields(1).Value
       Range("a2") = rs.Fields(2).Value
       Range("a3") = rs.Fields(3).Value
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Trevor G]

Hi Dave,

I would look to create a query in the Database and use a parameter box. Access equivalent to an InputBox in a query something like this Like[Enter the PO Number]&"*" so you can get the PO Number, once tested in the database you can then add a macro in the database to export the data, then use Excel VBA to Run the Macro.

 

[Micron]

Not a regular user of ADO, but to me it looks like your code does nothing with the input box value. I imagine you think that you are filtering the recordset based on "Master" whatever that is, but I don't foresee that happening. If you check, you are probably pulling in the first record of whatever Master is. You might try
rs.Filter = PO <-- which you have not declared, which means you are not using Option Explicit in your code. Note: of course, apply filter after opening rs.

Again, ADO not my thing so you might want to research how to open rs filtered (can use sql statement) or how to apply a filter once you've opened the rs.

 

[SQUIDD]

Thanks all for your help, i have moved in a different direction to continue with excel as a database.

dave