Automate Import-Export in Access

[NarayanaR]

Hello,

I'm new to Office(!) but not to programming. I have a requirement that I'm struggling to solve:

1. In Access, I import a List from a sharepoint site as a linked table.
2. I delete all the data in the list.
3. I import an Excel file from my local system onto this linked table.
4. Save and close.

How can I automate this?

I've tried:
1. Macro->Open Table, but this does not fetch an http:// link
2. Save Import Steps, but this option not available for Linked tables.

Is there a way I can do this via command line? Are Access functions available thru cmd ?

I'm using Access 2007 btw.

Thanks and will appreciate any response :)
Narayana

[MMcCarthy]

There are a number of options available... one of which is programmatically linking or relinking tables.

Private Function ReconnectTables() As Boolean
On Error Resume Next
Dim tdf As DAO.TableDef
Dim dbs As DAO.Database
Dim strPath As String
Dim strConnect As String
 
    Set dbs = CurrentDb
    'Set the path to the backend
    strConnect = "\\full path to source file"
    'Reconnect the tables
    For Each tdf In dbs.TableDefs
        If tdf.Connect = "Linked Table Name" Then ' substitute the name of your linked table
            tdf.Connect = ";DATABASE=" & strConnect
            tdf.RefreshLink
        End If
    Next
 
    Set dbs = Nothing
    If Err.Number = 0 Then ReconnectTables = True
 
End Function
 

[Glynn]

@msquared
Will this work in the opposite direction?

In Access, I have a linked Excel Spreadsheet.
I have a query that selects unique records from the Excel Spreadsheet.
I have exported the output of the select as an XML file to SharePoint site, and saved the Export.
What I would like to do is automate the running of the "saved export"

Any thoughts?

Thanks in advance

[MMcCarthy]

Sorry for the delay in replying, I was away with work this week.

I'm not sure exactly what you are trying to do. If you are just appending data to a table then you can just run the query in code. However, if you are trying to create an xml file then the only way I know of to do this is by Writing to a text file. Does the following code help ? I used something similar to create a labelling xml file recently.

Function LabelsToXML(label As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDestFile As String
Dim iFileNum As Integer
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM Linked_Excel_File")
    strDestFile = "Path to new xml File"
    iFileNum = FreeFile
 
 
    Open strDestFile For Output As #iFileNum
    ' If an error occurs report it and end.
    If Err <> 0 Then
        MsgBox "Cannot open filename " & strDestFile
        End
    End If
 
    Print #iFileNum, "<?xml version=""" & "1.0" & """ standalone=""" & "no""" & "?>"
    Print #iFileNum, "<labels _QUANTITY=""" & "1" & """ _PRINTERNAME=""" & strPrinter & """>"
    Print #iFileNum, "<label _FORMAT=""" & strLabel & """ _QUANTITY=""" & qty & """>"
 
        ' do this for each field
        Print #iFileNum, "<variable name=""" & rs1.Fields("FieldName or index").Name & """>" & rs1.Fields("FieldName or index").Value & "</variable>"
 
    Print #iFileNum, "</label></labels>"
    Close #iFileNum
 
End Function
 

[Glynn]

Thank you for your reply. My solution was to create a query against the linked in Excel spreadsheet which was XML exported to the sharepoint site.

Then I saved the export xml and initiated it with a DoCmd.RunSavedImportExport, which worked.

Thanks again,

Glynn

[MMcCarthy]

Glad you got it working. I forgot 2007 had an export to xml facility. Unfortunately the application I was working on was in 2003.

Mary