Solved

Rename a table when importing from XML using Access VBA

Posted on 2013-12-04
3
897 Views
Last Modified: 2013-12-10
Hi experts,
I have created a form in access that imports an XML file to my database.  I would like to rename the table every time I import the XML file.  Heres my current code.  How would I go about renaming the XML table to 'note' every time I import it?  

Private Sub Command10_Click()

Dim File As Variant
File = GetFile()
If IsNull(File) Then
    MsgBox "Nothing was selected", vbOKOnly
Else
    Me.FName = File


Application.ImportXML _
    DataSource:=Me.FName, _
    ImportOptions:=acStructureAndData
 
End If
End Sub

Open in new window


Thanks
0
Comment
Question by:Lobb
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39697872
You can use the rename method, which works like this:

docmd.Rename "New Name",ObjectType,"Old Name"

Applied to your code (Note is a reserved word, so I'm using "tblNote" for the table name instead):


Private Sub Command10_Click()

Dim File As Variant
dim s as string

File = GetFile()
If IsNull(File) Then
    MsgBox "Nothing was selected", vbOKOnly
Else
    Me.FName = File


Application.ImportXML _
    DataSource:=Me.FName, _
    ImportOptions:=acStructureAndData

' Extract the table name (which is the filename without the path or extension)

 s = Me.FName
 
 ' Remove the folder path
 s =  mid(s,instrrev(s,"\") +1)

 ' Remove the extension
 s = split(s,".")(0)

 ' Rename the table, using the derived file name as the Old Table Name
 Docmd.Rename "tblNote", acTable, s
 
End If
End Sub

Open in new window

0
 

Author Comment

by:Lobb
ID: 39699298
Thanks for the help so far.  Every time I go to upload my file it says 'Microsoft can not find the object 'example'

I don't know the table name or file name of the files that I am uploading.  I just hope to rename the table every time I upload an xml file to the same name.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39700563
Is it failing on the code I added, or on the import statement?

How are you selecting the file to import?  Are you using a file dialog?

When you use ImportXML, it *should* create a table having the same name as the file that you are importing (ie: "test.XML" will automatically import into a table called "test").  So if you are browsing to a file, the table created will be given the same name as the file you browse to.  

If possible, please post a sample copy of your database - including just the relevant form/tables/etc, and with any personal or sensitive data removed, and also post a sample XML file.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question