Access Newbie Needs Macro Help!

[ramasterre]

Hello All

I've learned quite a bit from this forum on how to write up macros in excel but now I need to work in Access.

I'm basically trying to automate a process without changing the current process so much that the folks who will be using it will not be happy (touchy folks). I've looked through (and printed) numerous postings trying to find the proper way to proceed to no avail. I have only been able to piece together bits of info that will probably only be helpful once I understand how and where to start. With that said I need to do the following:

Currently, I have a macro that downloads two .cvs files from a shared drive, formats and edit them and then saves them to a specified directory. From here I need to have them imported into access (or linked--btw I have no clue how to do that) so that they can be set up in an unmatched query and then exported back into excel (its what the folks prefer to have it in) where another macro sorts and edits it. Ideally, I would like this process to flow smoothly (and quickly).

I know little of vba in access (though I have been playing around it in to learn) and I know even less of SQL. I have some code to import/export the data back and forth but I really need a good start in how to write up code to open run the query.

Any help would be GREATLY appreciated!

 

[SydneyGeek]

IF you want to do most of the analysis in Excel, you can do what you want by doing the following:

1. Always save the .csv files to the same directory, same name
2. In Access, File | Get External Data | Link... Select the file type, navigate to the directory with the csv's and select them both (Ctrl-Click), then OK.
3. Build the query to show your mismatches
4. In Excel, use MS QUERY to return the mismatched data. You'll need to point it at the mismatch query, select all fields, then choose to return the query results to Excel. Next time the files are updated, refresh the Excel query (manually or with code).

This way, there is no need for the users to directly interact with Access. It just does the job in the background.

Hope that helps

Denis

 

[ramasterre]

Thanks so much for the Step-By-Step!! It makes alot more sense to me now.