By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

Automating Data export and import in Access

P: 68
Database A is distributed across 21 different agencies in a wide geographic area. They all use Access 2007 to run the database. Head office has a copy of database A and would like to have it store all the data from all its outlying agencies. This would require each agency to export the data to a text file and email it to head office. The user at head office would then import this data into the existing database appending all records to its appropriate table.

I want to make this process a one click option. Is there a way in which I can create a button with a procedure that exports all the data that was added to the database since the last export?

I am also looking for a one click option to import the data back into the database at head office.

Surly someone had done this before… Please any assistance would be greatly appreciated
Jan 11 '10 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
OK so have you looked at exporting a query as a text file. You would need a query to refine the data to that added since last export. This assumes that you have a unique identifier that is an autoincrement number as in you could store the id of the last record sent and design a query to show only those records with an id greater than that. You would need to add a table to store the value of the last id sent.

You could then set up an export of that query and save the export steps which 2007 allows you to do.

Now look at the DoCmd.TransferText function.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acExportDelim, "Saved Export Specification", "TableName", "Exported File Name with Full Path", True
  2.  
Jan 11 '10 #2

NeoPa
Expert Mod 15k+
P: 31,489
If it's to be imported automatically, at least the process handled by the code, after being sent to head-office, then it must be done in a standard way. The files must have standard names and be stored into a standard folder (or folder structure).

Actually, like many posters, you seem sure your understanding of how straightforward it should be must be right, without any real understanding of the issues. This can be done of course, but it will need consideration and planning if it's not to end up a mess. If you can put down some of your (more precise) thinking then progress can be made, but this is a project in itself. This is not something Access has, or even should have really, ready as a bolt-on.
Jan 11 '10 #3

nico5038
Expert 2.5K+
P: 3,072
Microsoft did create in the past the "distributed" database design using replication, something that did cause a lot of trouble...
For Access 2007 Microsoft created/expanded the Sharepoint server approach to handle this type of data exchange situations. The downside is however the additional cost :-(

Using the above proposed approach of exporting the essential data and importing it into the "master" database will require the addition of the agency code to all basic data and could be automated by sending the result to an ftp location so collecting the data can be handled "easily"

Nic;o)
Jan 11 '10 #4

P: 68
Thanks for the reply guys for your insights. I really do appriciate your help. I am rapping my head arround the concepts shared here and will get back to you as sooon as problems arise, and I'm sure that they will.

NeoPa it is not that I am sure I have things covered it is just that I dont know where to start... so I just jump in and have you guys slap me in line when I stray. I'll try to post a proposed design when I finish one. In the mean time if there are any more suggestions then I'll be glad to see it.

OH!! Have a programmatically, creatively, geekely successful 2010
Jan 13 '10 #5

NeoPa
Expert Mod 15k+
P: 31,489
You don't necessarily need a perfected solution Ken. We can help and work with you, but there must be something there to start with first.

Did you say slap you in line? Surely you mean give a gentle nudge in the right direction with our kid gloves on :D

Have a wonderful New Year :)
Jan 14 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
How many different tables are we talking about? How complex a database is it?
Jan 14 '10 #7

P: 68
I have about ten tables to export.

I have been trying various methods to do this process over the pass week and was not so successful.
I am now running simple imports through the access 2007 wizards and getting problems. When i try importing a text file that I eariler exported from the same table I get an error about primary key violation and if I leave out the primary key field I get another type of error.

Can any one say how do I treate my export data so that it can be imported into the same table with out primary key or other issues?
Jan 22 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
Do you want the imported data to OVERWRITE what you allready have stored? Or be added as new rows at the bottom?
Jan 22 '10 #9

P: 68
No I want to append the data to the existing table
Jan 22 '10 #10

NeoPa
Expert Mod 15k+
P: 31,489
@KingKen
Ken,

Without more specific information it's hard to help. Certainly AutoNumber fields are a problem in such cases, but I have no way of knowing if this is your issue. Perhaps if you could submit the question fully with the necessary detail and explanation of what you're doing and struggling with we could help more effectively.

It may be better if it's specified clearly in a separate thread (to avoid confusion with the main topic of this one), but add a link in here if you like so that experts familiar with your issues can go there directly.
Jan 24 '10 #11

TheSmileyCoder
Expert Mod 100+
P: 2,321
What I have done in similar cases is to write VBA code that imports the spreadsheet the into temporary tables, then use (from vba) append query's to append the new records to the table. If you 10 tables hold some kind of relationsship you may have to use some temporary keys as well to make sure it runs properly.

This is however a rather large task to just describe in a forum post. What I can do is provide some sample code and maybe you can have a go at it yourself, and ask more questions as you go.

The process is in a few steps.
  1. First I have some code to open a filebrowser dialog to select the file I want to import, and then I store this in a unbound textbox
  2. User then clicks the "go" button which checks if required data has been filled in.
  3. The excel sheet is opened and some code is run to delete extra columns (Users has a nasty tendency to write stuff where they shouldn't)
  4. Code then imports to a temporary table tbl_ReturnComments
  5. Code then transfers the records to the main table
This may seem like alot of work, but if you want to automate the import of 10 tables, I would expect that you allready knew it wouldn't be a walk in the park.
You would have to modify/add code for each sheet you want to import.
This is not intended as a solution to your problem, just as example code to get you going.

1.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_GetFileName_Click()
  2. '************************************************************************
  3. 'Lets get the file name
  4.     Debug.Print "Getting File Name"
  5.     'Declare a variable as a FileDialog object.
  6.     Dim fd As FileDialog
  7.  
  8.     Dim strFilePath As String
  9.     'Create a FileDialog object as a File Picker dialog box.
  10.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  11.  
  12.     'Declare a variable to contain the path
  13.     'of each selected item. Even though the path is a String,
  14.     'the variable must be a Variant because For Each...Next
  15.     'routines only work with Variants and Objects.
  16.     Dim vrtSelectedItem As Variant
  17.  
  18.     'Use a With...End With block to reference the FileDialog object.
  19.     With fd
  20.         .InitialFileName = strComPath
  21.         .AllowMultiSelect = False
  22.         .Filters.Clear
  23.         .Filters.Add "Excel files", "*.xls", 1
  24.         'Use the Show method to display the File Picker dialog box and return the user's action.
  25.         'The user pressed the action button.
  26.         If .Show = -1 Then
  27.                 strFilePath = .SelectedItems(1)
  28.             'Step through each string in the FileDialogSelectedItems collection.
  29.             'For Each vrtSelectedItem In .SelectedItems
  30.  
  31.                 'vrtSelectedItem is a String that contains the path of each selected item.
  32.                 'You can use any file I/O functions that you want to work with this path.
  33.                 'This example simply displays the path in a message box.
  34.              '   strFilePath: " & vrtSelectedItem
  35.  
  36.             'Next vrtSelectedItem
  37.  
  38.         Else
  39.             'The user pressed Cancel.
  40.             DoCmd.Hourglass (False)
  41.             MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
  42.             Set fd = Nothing
  43.             Exit Sub
  44.         End If
  45.     End With
  46.  
  47.  
  48.     'Debug.Print strFilePath
  49.     Me.tb_FileName =strFilePath
  50.     'Set the object variable to Nothing.
  51.     Set fd = Nothing
  52. End Sub
2+3+4+5.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Go_Click()
  2.     'Imports the comments
  3.  
  4.     'First check that there required info is provided
  5.     If IsNull(Me.tb_FileName) Or IsNull(Me.cmb_Author) Or IsNull(Me.cmb_ComType) Then
  6.             'Missing required info
  7.             MsgBox "You have not filled in all the required information", vbOKOnly + vbExclamation, "Error 40"
  8.             Exit Sub
  9.         Else
  10.             'Proceed
  11.     End If
  12.     Debug.Print "Importing external comments"
  13.     'Clear the table
  14.     DoCmd.SetWarnings (False)
  15.     DoCmd.RunSQL "DELETE * FROM tbl_ReturnComments"
  16.     DoCmd.SetWarnings (True)
  17.  
  18.  
  19.     'Clean up spreadsheet for transfer
  20.     Dim myexcel As New Excel.Application
  21.     Dim myWb As Excel.Workbook
  22.     Set myWb = myexcel.Workbooks.Open(strComPath & Me.tb_FileName)
  23.     Dim mySheet As Excel.Worksheet
  24.     Set mySheet = myWb.Sheets(1)
  25.     'Unlock it
  26.     mySheet.Unprotect ("CityringenRMT")
  27.     mySheet.Columns("G:M").Delete
  28.     mySheet.Range("F1") = "Comments"
  29.     myWb.Save
  30.     myWb.Close
  31.     myexcel.Quit
  32.     Set myexcel = Nothing
  33.  
  34.     'Transfer spreadsheet to a temporary file:
  35.  
  36.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_ReturnComments", Me.tb_FileName, True
  37.     'Counts the comments
  38.     Dim intNrComments As Integer
  39.     intNrComments = DCount("Comments", "tbl_ReturnComments")
  40.  
  41.     'Transfer to real comments table
  42.     Dim strSQL As String
  43.     strSQL = "INSERT INTO tbl_Comments ( idKrav, strKravRevision, memComment, idCommentBy, dtCommentCreated, id_CommentType, idCommentStatus, dtChangedOn ) " & _
  44.     "SELECT tbl_ReturnComments.[Req# ID], tbl_ReturnComments.[Rev#], findnewlines([Comments]) AS memCom, " & _
  45.     Me.cmb_Author & " AS Author, Now() AS dtCreated, " & Me.cmb_ComType & " AS ComType, 1 as idStatus, Now() " & _
  46.     " FROM tbl_ReturnComments" & _
  47.     " WHERE (((tbl_ReturnComments.Comments) Is Not Null));"
  48.  
  49.  
  50.  
  51.  
  52.     DoCmd.SetWarnings (False)
  53.     DoCmd.RunSQL strSQL, True
  54.     DoCmd.SetWarnings (True)
  55.  
  56.     MsgBox "Import of " & intNrComments & " comments was succesfull"
  57.  
  58.  
  59. End Sub
Jan 25 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.