?
Solved

Rename a table when importing from XML using Access VBA

Posted on 2013-12-04
3
Medium Priority
?
988 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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