Simplify Importing Data into Access

[freeskier]

Hello,

I am looking for some advice on collecting data. We are working on collecting about 20 different data points from 60 initial people, later to be expanded to approximately 200. At present we have developed an excel spreadsheet which is to be distributed, have the information entered, and then have the file returned to be imported into an Access database. It is important to keep it in an excel spreadsheet because it offers immediate feedback to the user. Feedback includes charts/graphs and some data constraints.

I have been looking into posting the spreadsheet on a webpage and having it connect to MySQL but it appears to lose some functionality when in HTML.

Basically I am just interested if anyone can offer advice on simplifying the procedure. I am not that interested in writing long VBA that will import all spreadsheets automatically. I have seen some programs which will do multiple imports for you but I don't have any experience with them.

Any advice anyone has is greatly appreciated! Thanks for your time.

[NeoPa]

I could only suggest automating it with VBA.
I wouldn't think it would be particularly long or complicated, but it would be VBA.
Unless of course, you used macros. Can't help there though, always use VBA myself.

[MMcCarthy]

Hello,

I am looking for some advice on collecting data. We are working on collecting about 20 different data points from 60 initial people, later to be expanded to approximately 200. At present we have developed an excel spreadsheet which is to be distributed, have the information entered, and then have the file returned to be imported into an Access database. It is important to keep it in an excel spreadsheet because it offers immediate feedback to the user. Feedback includes charts/graphs and some data constraints.

I have been looking into posting the spreadsheet on a webpage and having it connect to MySQL but it appears to lose some functionality when in HTML.

Basically I am just interested if anyone can offer advice on simplifying the procedure. I am not that interested in writing long VBA that will import all spreadsheets automatically. I have seen some programs which will do multiple imports for you but I don't have any experience with them.

Any advice anyone has is greatly appreciated! Thanks for your time.

Have you tried linking the spreadsheets into access rather than importing them. Just the data sheets of course. Access to all intents and purposes treats them as a table and you can then move the data over to odbc linked sql tables or keep it in access.

[NeoPa]

I would certainly recommend that unless there are particular reasons, in your situation, that would make it innappropriate.

[freeskier]

Thank you for your response. What benefit does linking provide over importing? It does not seem to allow multiple selections at once for either function. Unless you are suggesting that I have the files accessible on a networked location and then write some vba to link all the files to Access?

Have you tried linking the spreadsheets into access rather than importing them. Just the data sheets of course. Access to all intents and purposes treats them as a table and you can then move the data over to odbc linked sql tables or keep it in access.

[MMcCarthy]

Thank you for your response. What benefit does linking provide over importing? It does not seem to allow multiple selections at once for either function. Unless you are suggesting that I have the files accessible on a networked location and then write some vba to link all the files to Access?

That would be my suggestion.

Create a folder to hold the relevant files and then write some vba code to get a list of files in directory and link them to access. There are a number of advantages to linking over importing.

Size is one.

Another is it is very difficult sometimes to reconcile formats of some values from excel when importing them into access. Linking them and using an access make table query to build your new table(s) or an append query to append data to an existing table allows for better formating control.

[freeskier]

thank you, it's very helpful to be able to discuss with experienced professionals.

[MMcCarthy]

thank you, it's very helpful to be able to discuss with experienced professionals.

You're welcome.

If you run into any problems, let us know and we'll see if we can help.