Solved

Rename a table when importing from XML using Access VBA

Posted on 2013-12-04
3
812 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now