Auto import data into Access

Question

Auto import data into Access

GF

Hall of Fame

Points: 3774
More actions
December 13, 2005 at 6:04 pm

#76462

We have a project for a customer who insists on being able to download data from the website where we manage their data into their access db located on some machine in their office.
I have lost the argument against this so I need to find some answers to a few issues.
I know I can import files into access by using the "get external data" choice on the menu. However, the customer wants this process automated. Does anyone have or can anyone point me to some tool or scripts that will auto mate the process?

Thanks in advance.

Gary

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 1

In which Access version?

We've been using docmd.transfertext in a VBA module to import text-files into tables in Access 97 using a import-specification (takes only 1 time to make them and are stored in the system tables)

smknox Hall of Fame Points: 3275 More actions · Answer 2

Hi GF:

Here is a code snippet I use to export tables. I modified for import into Access. This would be in some module within the Access app.

If vba is not familiar, the key will be to look in the help file under DoCmd.TransferDatabase (press F1 with cursor on that line). Hope that helps. If you need more explanation please post back.

' Import selected tables.

SysCmd acSysCmdSetStatus, "Importing tables ..."

DoEvents

Application.Echo False

strCriteria = "ImportName='" & pstrImportName & "' AND ObjectType=" & acTable & " AND "

For Each ao In CurrentData.AllTables

If Not IsNull(DLookup("ImportName", "tsDA_ImportControl", _

strCriteria & Chr(34) & ao.Name & Chr(34) & " Like Pattern")) _

Then

DoCmd.TransferDatabase acImport, "Microsoft Access", strExportPath, acTable, ao.Name, ao.Name

End If

Next ao

Application.Echo True

Sam

Bill Holt Mr or Mrs. 500 Points: 527 More actions · Answer 3

Assuming you are storing the data from the website in SQL Server, why not just attach the SQL tables to the Access database. All that is then necessary is a query or queries to push the data into local tables (if necessary) or just run a report that provides the information the client needs to know.

If their website data is offsite then perhaps that is a more complex matter.

Bill Holt

Chattanooga, TN

swjohnson Hall of Fame Points: 3254 More actions · Answer 4

Here is a rough outline for what we have one of our clients do.

1. They click on a button on a web page.

2. This button is tied to a stored procedure (for authentification/security purposes and so they can pass certain parameters to control which data they download) and it fires off a DTS job (you can use the data export wizard to help with the DTS routine) that will export their data to an Access database and it is then moved to their FTP folder.

3. From there they can download the Access database via an FTP client.

Everyone is happy and our server is still secure.

SJ

Accepting all invites

GF Hall of Fame Points: 3774 More actions · Answer 5

Thanks to all for your responses, they all show merit. I am researching each of the suggestions to see if they are acceptable to our sys admins and our customers.

Thanks again.

Gary