[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Rename a table when importing from XML using Access VBA

Posted on 2013-12-04
3
Medium Priority
?
1,031 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

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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