Database script

[kleclark]

Hi,

Problem/Summary: can access 2007 use script to import data

Background:
I am very new to setting up a database - any sort. I am part of influenza surveillance network across the US. I am not familiar enough with using script etc to use mysql/command line/php administrator



My predecessor also lacked the knowledge to setup databases and set our data up in Access. Unfortunately, it has been agreed it has not been done in a way for us to be able to readily analyze our data.

I am working with a collaborator who has developed a script for use in mysql which imports deduplicates etc his data (very similar to our data) and imports it into his database - this database has been recommended for our purpose.

We recently paid for a database consultant to setup an Access database compatible with the mysql design of our above colleague. However, the contractor did not import the data in.

Problem:

What would be the best way to proceed? The options that have been posed are:

1. write a script (or adapt the script from our colleague) to import data into Access - is this possible?? My colleague can provide the script he has written, can you recommend a consultant in central and or northern California?

2. Create a mastersheet in excel (not linked to other excel worksheets) and link to access - can this be done? What are the implications? We will be constantly adding surveillance events?

3. Create a single mastersheet in excel from multiple excel worksheets and link to access? We have another colleague who has done this using Vlookup and hlookup but as the amount of data collected increases the system becomes slower and slower.

4. Copy and paste into Access? Very time consuming also I would be concerned we could not remove duplicates effectively.

5. Any other thoughts?

I am sorry for the very long blog. I greatly appreciated any suggestions.

Kerri

[SteveF]

Please clarify: where is the data now that you want to import into Access? It's unclear to me whether it's in Excel or MySQL.

Actually, for either one, I'd recommend the following:

1. Link to the source data from within Access. For MySQL, you'll probably need to download, install and configure MySQL's ODBC driver.

2. Run queries to transfer the data to native Access tables. (this is actually optional if the data resides in MySQL)

3. If you haven't already, build forms, queries & reports in Access that allow you to enter, maintain and analyze your data.

I might know an Access consultant in Central CA. I'll have to get in touch with him & see if he's available.

Steve

[kleclark]

Hi Steve,

1. Our data is currently in excel sheets. We have also copied and pasted most of the data into tables in Access which are not appropriate for our analysis and do not match the the table set up our colleague is using in mysql.

2. Run queries to transfer the data to native Access tables - Is this the same as running a script?

2. We have not set up build forms, queries & reports in Access that allow you to enter, maintain and analyze your data so I would greatly appreciate a recommendation for a consultant.

Thank you very much for your help.