Automating Data export and import in Access

**MMcCarthy** · Jan 11 '10, 06:10 PM

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.TransferT ext function.

Code:

DoCmd.TransferText acExportDelim, "Saved Export Specification", "TableName", "Exported File Name with Full Path", True

**NeoPa** · Jan 11 '10, 07:26 PM

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.

**nico5038** · Jan 11 '10, 09:28 PM

Microsoft did create in the past the "distribute d" 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)

**KingKen** · Jan 13 '10, 07:48 PM

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 programmaticall y, creatively, geekely successful 2010

**NeoPa** · Jan 14 '10, 01:37 AM

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 :)

**TheSmileyCoder** · Jan 14 '10, 08:34 AM

How many different tables are we talking about? How complex a database is it?

**KingKen** · Jan 22 '10, 03:04 PM

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?

**TheSmileyCoder** · Jan 22 '10, 04:55 PM

Do you want the imported data to OVERWRITE what you allready have stored? Or be added as new rows at the bottom?

**KingKen** · Jan 22 '10, 07:47 PM

No I want to append the data to the existing table

**NeoPa** · Jan 24 '10, 08:20 PM

Originally posted by KingKen

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?

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.

**TheSmileyCoder** · Jan 25 '10, 09:24 AM

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.

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
User then clicks the "go" button which checks if required data has been filled in.
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)
Code then imports to a temporary table tbl_ReturnComme nts
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.

Code:

Private Sub btn_GetFileName_Click()
'************************************************************************
'Lets get the file name
Debug.Print "Getting File Name"
'Declare a variable as a FileDialog object.
Dim fd As FileDialog

Dim strFilePath As String
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
.InitialFileName = strComPath
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel files", "*.xls", 1
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
strFilePath = .SelectedItems(1)
'Step through each string in the FileDialogSelectedItems collection.
'For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
' strFilePath: " & vrtSelectedItem

'Next vrtSelectedItem

Else
'The user pressed Cancel.
DoCmd.Hourglass (False)
MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
Set fd = Nothing
Exit Sub
End If
End With

'Debug.Print strFilePath
Me.tb_FileName =strFilePath
'Set the object variable to Nothing.
Set fd = Nothing
End Sub

2+3+4+5.

Code:

Private Sub btn_Go_Click()
    'Imports the comments
    
    'First check that there required info is provided
    If IsNull(Me.tb_FileName) Or IsNull(Me.cmb_Author) Or IsNull(Me.cmb_ComType) Then
            'Missing required info
            MsgBox "You have not filled in all the required information", vbOKOnly + vbExclamation, "Error 40"
            Exit Sub
        Else
            'Proceed
    End If
    Debug.Print "Importing external comments"
    'Clear the table
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL "DELETE * FROM tbl_ReturnComments"
    DoCmd.SetWarnings (True)
    
    
    'Clean up spreadsheet for transfer
    Dim myexcel As New Excel.Application
    Dim myWb As Excel.Workbook
    Set myWb = myexcel.Workbooks.Open(strComPath & Me.tb_FileName)
    Dim mySheet As Excel.Worksheet
    Set mySheet = myWb.Sheets(1)
    'Unlock it
    mySheet.Unprotect ("CityringenRMT")
    mySheet.Columns("G:M").Delete
    mySheet.Range("F1") = "Comments"
    myWb.Save
    myWb.Close
    myexcel.Quit
    Set myexcel = Nothing
    
    'Transfer spreadsheet to a temporary file:
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_ReturnComments", Me.tb_FileName, True
    'Counts the comments
    Dim intNrComments As Integer
    intNrComments = DCount("Comments", "tbl_ReturnComments")
    
    'Transfer to real comments table
    Dim strSQL As String
    strSQL = "INSERT INTO tbl_Comments ( idKrav, strKravRevision, memComment, idCommentBy, dtCommentCreated, id_CommentType, idCommentStatus, dtChangedOn ) " & _
    "SELECT tbl_ReturnComments.[Req# ID], tbl_ReturnComments.[Rev#], findnewlines([Comments]) AS memCom, " & _
    Me.cmb_Author & " AS Author, Now() AS dtCreated, " & Me.cmb_ComType & " AS ComType, 1 as idStatus, Now() " & _
    " FROM tbl_ReturnComments" & _
    " WHERE (((tbl_ReturnComments.Comments) Is Not Null));"




    DoCmd.SetWarnings (False)
    DoCmd.RunSQL strSQL, True
    DoCmd.SetWarnings (True)
    
    MsgBox "Import of " & intNrComments & " comments was succesfull"
    
    
End Sub