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

Automate Import-Export in Access

P: 1
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
Jan 23 '08 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
There are a number of options available... one of which is programmatically linking or relinking tables.

Expand|Select|Wrap|Line Numbers
  1. Private Function ReconnectTables() As Boolean
  2. On Error Resume Next
  3. Dim tdf As DAO.TableDef
  4. Dim dbs As DAO.Database
  5. Dim strPath As String
  6. Dim strConnect As String
  7.  
  8.     Set dbs = CurrentDb
  9.     'Set the path to the backend
  10.     strConnect = "\\full path to source file"
  11.     'Reconnect the tables
  12.     For Each tdf In dbs.TableDefs
  13.         If tdf.Connect = "Linked Table Name" Then ' substitute the name of your linked table
  14.             tdf.Connect = ";DATABASE=" & strConnect
  15.             tdf.RefreshLink
  16.         End If
  17.     Next
  18.  
  19.     Set dbs = Nothing
  20.     If Err.Number = 0 Then ReconnectTables = True
  21.  
  22. End Function
  23.  
Feb 5 '08 #2

P: 5
@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
Dec 10 '09 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Function LabelsToXML(label As Integer)
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim strDestFile As String
  5. Dim iFileNum As Integer
  6.  
  7.     Set db = CurrentDb
  8.     Set rs = db.OpenRecordset("SELECT * FROM Linked_Excel_File")
  9.     strDestFile = "Path to new xml File"
  10.     iFileNum = FreeFile
  11.  
  12.  
  13.     Open strDestFile For Output As #iFileNum
  14.     ' If an error occurs report it and end.
  15.     If Err <> 0 Then
  16.         MsgBox "Cannot open filename " & strDestFile
  17.         End
  18.     End If
  19.  
  20.     Print #iFileNum, "<?xml version=""" & "1.0" & """ standalone=""" & "no""" & "?>"
  21.     Print #iFileNum, "<labels _QUANTITY=""" & "1" & """ _PRINTERNAME=""" & strPrinter & """>"
  22.     Print #iFileNum, "<label _FORMAT=""" & strLabel & """ _QUANTITY=""" & qty & """>"
  23.  
  24.         ' do this for each field
  25.         Print #iFileNum, "<variable name=""" & rs1.Fields("FieldName or index").Name & """>" & rs1.Fields("FieldName or index").Value & "</variable>"
  26.  
  27.     Print #iFileNum, "</label></labels>"
  28.     Close #iFileNum
  29.  
  30. End Function
  31.  
Dec 18 '09 #4

P: 5
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
Dec 18 '09 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 18 '09 #6

Post your reply

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