Solved

Automate communication between Access and Excel

Posted on 2004-08-31
8
308 Views
Last Modified: 2006-11-17
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_660000
35613     Industrial 2002-2005     Jul_02_199_ASHTONBEE_675000
22195     Industrial Pre-2002     Jan_01_60_MODERN_750000
28414     Industrial Pre-2002     Oct_01_403-405_MIDWEST_775000
26625     Industrial Pre-2002     Jul_01_84-86_CROCKFORD_858700

Thanks for any suggestions you can offer.
0
Comment
Question by:DanielAttard
  • 3
  • 3
  • 2
8 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11946928
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?
0
 
LVL 12

Accepted Solution

by:
fulscher earned 500 total points
ID: 11947145
I think you can do this, although it requires quite a bit of VBA programming.

I assume you have a button in your Excel sheet to update the Access DB. So you would do something like the following:

Dim i as integer
Dim RS as Recordset
Dim DB as Database

Set DB =Workspaces(0).OpenDatabase(<path>)
Set RS = DB.OpenRecordset("SELECT * FROM tblSales")

For i = 0 to <maximum lines in sheet>
  c = Range(i, 2)
  if c.Height > 0 then   ' visible
    RS.FindFirst(SalesID = " & c.Value)
    If Not RS.NoMatch Then
       RS.Edit
       RS("Select") = True
       RS.Update
    Endif
  Endif
Next


This is of course very simplified, but might get you started...

Jan
   
0
 

Author Comment

by:DanielAttard
ID: 11948291
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).OpenDatabase(<path>)

What is the exact syntax for that line?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11948395
In that case, why not just give the users an Access form to edit the data?
0
 

Author Comment

by:DanielAttard
ID: 11948415
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.  
0
 

Author Comment

by:DanielAttard
ID: 11948430
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("Z:\Data\OPTC_StatData.MDB")
Set RS = DB.OpenRecordset("SELECT * FROM tblSales")

For i = 0 To 10
  c = Range(i, 2)

Do I need to define 'c'?
0
 
LVL 12

Expert Comment

by:fulscher
ID: 11949368
Dim c as range

Jan
0
 
LVL 12

Expert Comment

by:fulscher
ID: 11974296
Thanks, Daniel!
Jan
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sum in Split Form 17 28
Access Schema 6 26
DataType for bit Comparison 11 21
Filter Date on Split Form 8 11
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question