Automate communication between Access and Excel
Hello Experts!
I need help with automating communication between Excel and Access. I have a number of Excel spreadsheets, in different workbooks, each containing hundreds of records that have been filtered down to disply only 5 to 10 records. What I need the macro to do is update specific records in an Access table as follows:
- update the 'Select' field (which is a Yes/No Check Box field) to No for any records that may have been previously set to Yes;
- the 'Select' field is in 'tblSales' which is always in the following location: Z:\Data\OPTC_StatData.MDB
- 'tblSales' has approximately 50,000 records, and I would like to set the value of the 'Select' field to Yes for only those records that are presently *displayed* on the active worksheet and identified by the 'Sale#'. The 'Sale#' column in my worksheet is always in Column B, although it is hidden. There may be hundreds of rows, but only 10 or so rows are visible. I hope this makes sense.
For example, I would like to update to Yes, only the records in tblSales that are shown below: 21050, 19935, 20631, etc.
Sale# SaleType ID
21050 Industrial Pre-2002 Nov_00_96_CROCKFORD_400000
19935 Industrial Pre-2002 Sep_00_103_MANVILLE_502294
20631 Industrial Pre-2002 Oct_00_36_CRANFIELD_575000
19519 Industrial Pre-2002 Aug_00_169_BARTLEY_618152
26357 Industrial Pre-2002 Jun_01_1744_MIDLAND_620000
38029 Industrial 2002-2005 Oct_02_330_NANTUCKET_66000
35613 Industrial 2002-2005 Jul_02_199_ASHTONBEE_67500
22195 Industrial Pre-2002 Jan_01_60_MODERN_750000
28414 Industrial Pre-2002 Oct_01_403-405_MIDWEST_775
26625 Industrial Pre-2002 Jul_01_84-86_CROCKFORD_858
Thanks for any suggestions you can offer.
I need help with automating communication between Excel and Access. I have a number of Excel spreadsheets, in different workbooks, each containing hundreds of records that have been filtered down to disply only 5 to 10 records. What I need the macro to do is update specific records in an Access table as follows:
- update the 'Select' field (which is a Yes/No Check Box field) to No for any records that may have been previously set to Yes;
- the 'Select' field is in 'tblSales' which is always in the following location: Z:\Data\OPTC_StatData.MDB
- 'tblSales' has approximately 50,000 records, and I would like to set the value of the 'Select' field to Yes for only those records that are presently *displayed* on the active worksheet and identified by the 'Sale#'. The 'Sale#' column in my worksheet is always in Column B, although it is hidden. There may be hundreds of rows, but only 10 or so rows are visible. I hope this makes sense.
For example, I would like to update to Yes, only the records in tblSales that are shown below: 21050, 19935, 20631, etc.
Sale# SaleType ID
21050 Industrial Pre-2002 Nov_00_96_CROCKFORD_400000
19935 Industrial Pre-2002 Sep_00_103_MANVILLE_502294
20631 Industrial Pre-2002 Oct_00_36_CRANFIELD_575000
19519 Industrial Pre-2002 Aug_00_169_BARTLEY_618152
26357 Industrial Pre-2002 Jun_01_1744_MIDLAND_620000
38029 Industrial 2002-2005 Oct_02_330_NANTUCKET_66000
35613 Industrial 2002-2005 Jul_02_199_ASHTONBEE_67500
22195 Industrial Pre-2002 Jan_01_60_MODERN_750000
28414 Industrial Pre-2002 Oct_01_403-405_MIDWEST_775
26625 Industrial Pre-2002 Jul_01_84-86_CROCKFORD_858
Thanks for any suggestions you can offer.
shanesuebsahakarn
You're editing the Excel data and then wanting to update the matching records in Access, right? Why don't you just import all of the records into Access and provide the user with an Access form in which they can edit the data directly as need be?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
ShaneS. - The records are already in access, so there is no need to import them. I just want to update a setting (from off to on) for the indicated records.
Jan - your idea got me started, but I am getting an error with the opening line.
Set DB =Workspaces(0).OpenDatabas
What is the exact syntax for that line?
ShaneS. - The records are already in access, so there is no need to import them. I just want to update a setting (from off to on) for the indicated records.
Jan - your idea got me started, but I am getting an error with the opening line.
Set DB =Workspaces(0).OpenDatabas
What is the exact syntax for that line?
In that case, why not just give the users an Access form to edit the data?
ASKER
I am giving them an access form, but I want to give them the form pre-filtered! with the correct records. I need to have the check-boxes turned on for the records identified in column B of the active worksheet. That's exactly what I need.
ASKER
I have turned on the reference to DAO 3.6 Library, so I am past the error I previously mentioned, but now I am getting a compile error saying 'Variable not defined' on this line:
c = Range(i, 2)
Here is the code so far:
Dim i As Integer
Dim RS As Recordset
Dim DB As Database
Set DB = Workspaces(0).OpenDatabase
Set RS = DB.OpenRecordset("SELECT * FROM tblSales")
For i = 0 To 10
c = Range(i, 2)
Do I need to define 'c'?
c = Range(i, 2)
Here is the code so far:
Dim i As Integer
Dim RS As Recordset
Dim DB As Database
Set DB = Workspaces(0).OpenDatabase
Set RS = DB.OpenRecordset("SELECT * FROM tblSales")
For i = 0 To 10
c = Range(i, 2)
Do I need to define 'c'?
Dim c as range
Jan
Jan
Thanks, Daniel!
Jan
Jan