Solved

Rename a table when importing from XML using Access VBA

Posted on 2013-12-04
3
955 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 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