updating access table using excel vba

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,692
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

VBA Code:
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,092
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 2, 2009
Messages
1,692
Thanks all for your help, i have moved in a different direction to continue with excel as a database.

dave
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,590
Members
417,152
Latest member
DayTimeSeby

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
Top