Import XML file to specific table (1 Viewer)

D

dalanaaccess

New member

Local time

Today, 15:13

Joined

Feb 13, 2018

Messages

7

• Feb 19, 2018

• #1

Hi All,

I have XML file that was exported from DB1, Table A using VBA Sub Application.ExportXML. I would like to import the same file to BD2, Table B where Table A and B are identical with the same fields structure and names.

Please help me if anyone have any idea.
Thank you.

 

Ranman256

Well-known member

Local time

Today, 18:13

Joined

Apr 9, 2015

Messages

4,339

• Feb 19, 2018

• #2

attach the xl file as an external table from a 'temp' file: c:\temp\File2Import.xls"
build a query to import the data into your target table
put the query in a macro.

then the steps are:
1. save and overwrite c:\temp\File2Import.xls"
2. run the macro.
done.

 

Cronk

Registered User.

Local time

Tomorrow, 08:13

Joined

Jul 4, 2013

Messages

2,771

• Feb 19, 2018

• #3

Ranman, I don't think it's possible to link to an XML file.

Anyway, easier to use
application.importXML,"xxx.xml", acStructureAndData

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Tomorrow, 06:13

Joined

May 7, 2009

Messages

19,227

• Feb 20, 2018

• #4

inside DB1 use this code:

Dim db As DAO.Database
Set db=Currentdb
db.Execute "INSERT INTO " _
& "[Table B] SELECT * FROM [Table A] IN 'c:\db\DB2';"

'''''
change c:\db\ to the correct path of of DB2

 

D

dalanaaccess

New member

Local time

Today, 15:13

Joined

Feb 13, 2018

Messages

7

• Feb 20, 2018

• #5

Ranman256 said:

attach the xl file as an external table from a 'temp' file: c:\temp\File2Import.xls"
build a query to import the data into your target table
put the query in a macro.

then the steps are:
1. save and overwrite c:\temp\File2Import.xls"
2. run the macro.
done.

Click to expand...

I did not understand why excel file when I asked for XML. Please explain if you can. Thank you.

 

D

dalanaaccess

New member

Local time

Today, 15:13

Joined

Feb 13, 2018

Messages

7

• Feb 20, 2018

• #6

arnelgp said:

inside DB1 use this code:

Dim db As DAO.Database
Set db=Currentdb
db.Execute "INSERT INTO " _
& "[Table B] SELECT * FROM [Table A] IN 'c:\db\DB2';"

'''''
change c:\db\ to the correct path of of DB2

Click to expand...

Well, I do not have 2 databases. I have one database and XML file with one record to add to database 1 in specific table.