trying to read datain excel from an access database

[justlearning123]

ive never really worked with access but what I am trying to do is have a macro that looks in the access file and the table is called "updates" and within that table I want it to look in the column called "station" and in the column called "date" and the column called "updates" to see if there is a match within a row.




so in my excel file range A2 has the station number so when the macro runs it looks at the value of cell A2 in the excel file and then trys to see if there is a match in the access file with todays date from "date" column and and the word UPDATED from the "update" column are in the same row together..

I know this probably sounds confusing... any questions just ask...

the excel path = C:\Users\user\Desktop\updates.xlsm
access path = C:\Users\user\Desktop\access updates.accdb

[ranman256]

Attach the excel file as a table.
build a query that joins the [date] to get your results.

[justlearning123]

I was trying to use a macro because I have about 60 of these excel files so it would be easier to copy the macro in each file thnaak you

[ranman256]

do you need code to scan the folder for all xl files and load them via macro?

[justlearning123]

no the code will be a macro in each excel workbook and when run it will open the access database to the table called updates and if the value of cell A2 in the excel file which would be in the "stations" column and todays date from "date" column and the word UPDATED from the "update" column are in the same row together.. if they are in the same row it will exit sub and if there isn't a match then a msgbox will appear that says "needs to be updated"

[ranman256]

Why would you open 60 workbooks and run 60 macros,
when you can open access and run 1 macro that will process 60 workbooks?

[June7]

Excel can pull data from Access by either linking or in VBA (Excel macro) connect to Access and query table/query object. Example:

Public Sub GetData()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='path to database\databasename.accdb'"
rs.Open "SELECT * FROM table/query WHERE some criteria here;", cn, adOpenStatic, adLockReadOnly
End Sub

Why is Excel involved at all?

[justlearning123]

excel is where all the associates keep track of their work station data and then it is transferred to access when it is updated so we can better look at the data... I know it probably sounds confusing im sorry

[justlearning123]

60 workbooks for 60 station each station has their own

[June7]

These users are all on the same network? Why not build multi-user database for direct input?

[justlearning123]

I would like to do that but I cannot make that call... they have years worth of work in the coding of the excel files VBA so they will only let me do it this way.. I know it seems "dumb" to do it the way that im trying to do it but that's all they will let me

[June7]

And what does that coding do?

[justlearning123]

a ton of different things from verifying things through calling up a lookup table and updating statuses into access keeping track of schedules...

[June7]

All of which can be done in Access. Maybe if you build it they will come?

[justlearning123]

That's a good thought.. I will try but like I said im not very familiar with MS access