ImportXML doesn't give me a table

N

Nevie and Phil

When I use the ImportXML method in VBA it doesn’t produce a table. I
thought that running this code would make a table named employees.



Here is my code ;

Dim sXMLPath As String
Dim appAccess As Object

Set appAccess = CreateObject("Access.Application")
sXMLPath = "C:\Documents and Settings\nevieandphil\My
Documents\Database\references\employees.xml"
appAccess.ImportXML DataSource:=sXMLPath, _
ImportOptions:=acStructureAndData



Here is the source that I am experimenting with.

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:eek:d="urn:schemas-microsoft-com:eek:fficedata">
- <Employees>
<EmployeeID>1</EmployeeID>
<LastName>Davolio</LastName>
<FirstName>Nancy</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Ms.</TitleOfCourtesy>
<BirthDate>1968-12-08T00:00:00</BirthDate>
<HireDate>1992-05-01T00:00:00</HireDate>
<Address>507 - 20th Ave. E. Apt. 2A</Address>
<City>Seattle</City>
<Region>WA</Region>
<PostalCode>98122</PostalCode>
<Country>USA</Country>
<HomePhone>(206) 555-9857</HomePhone>
<Extension>5467</Extension>
<Photo>EmpID1.bmp</Photo>
<ReportsTo>2</ReportsTo>
</Employees>
- <Employees>
<EmployeeID>2</EmployeeID>
<LastName>Fuller</LastName>
<FirstName>Andrew</FirstName>
<Title>Vice President, Sales</Title>
<TitleOfCourtesy>Dr.</TitleOfCourtesy>
<BirthDate>1952-02-19T00:00:00</BirthDate>
<HireDate>1992-08-14T00:00:00</HireDate>
<Address>908 W. Capital Way</Address>
<City>Tacoma</City>
<Region>WA</Region>
<PostalCode>98401</PostalCode>
<Country>USA</Country>
<HomePhone>(206) 555-9482</HomePhone>
<Extension>3457</Extension>
<Photo>EmpID2.bmp</Photo>
</Employees>
- <Employees>
<EmployeeID>3</EmployeeID>
<LastName>Leverling</LastName>
<FirstName>Janet</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Ms.</TitleOfCourtesy>
<BirthDate>1963-08-30T00:00:00</BirthDate>
<HireDate>1992-04-01T00:00:00</HireDate>
<Address>722 Moss Bay Blvd.</Address>
<City>Kirkland</City>
<Region>WA</Region>
<PostalCode>98033</PostalCode>
<Country>USA</Country>
<HomePhone>(206) 555-3412</HomePhone>
<Extension>3355</Extension>
<Photo>EmpID3.bmp</Photo>
<ReportsTo>2</ReportsTo>
</Employees>
- <Employees>
<EmployeeID>4</EmployeeID>
<LastName>Peacock</LastName>
<FirstName>Margaret</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Mrs.</TitleOfCourtesy>
<BirthDate>1958-09-19T00:00:00</BirthDate>
<HireDate>1993-05-03T00:00:00</HireDate>
<Address>4110 Old Redmond Rd.</Address>
<City>Redmond</City>
<Region>WA</Region>
<PostalCode>98052</PostalCode>
<Country>USA</Country>
<HomePhone>(206) 555-8122</HomePhone>
<Extension>5176</Extension>
<Photo>EmpID4.bmp</Photo>
<ReportsTo>2</ReportsTo>
</Employees>
- <Employees>
<EmployeeID>5</EmployeeID>
<LastName>Buchanan</LastName>
<FirstName>Steven</FirstName>
<Title>Sales Manager</Title>
<TitleOfCourtesy>Mr.</TitleOfCourtesy>
<BirthDate>1955-03-04T00:00:00</BirthDate>
<HireDate>1993-10-17T00:00:00</HireDate>
<Address>14 Garrett Hill</Address>
<City>London</City>
<PostalCode>SW1 8JR</PostalCode>
<Country>UK</Country>
<HomePhone>(71) 555-4848</HomePhone>
<Extension>3453</Extension>
<Photo>EmpID5.bmp</Photo>
<ReportsTo>2</ReportsTo>
</Employees>
- <Employees>
<EmployeeID>6</EmployeeID>
<LastName>Suyama</LastName>
<FirstName>Michael</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Mr.</TitleOfCourtesy>
<BirthDate>1963-07-02T00:00:00</BirthDate>
<HireDate>1993-10-17T00:00:00</HireDate>
<Address>Coventry House Miner Rd.</Address>
<City>London</City>
<PostalCode>EC2 7JR</PostalCode>
<Country>UK</Country>
<HomePhone>(71) 555-7773</HomePhone>
<Extension>428</Extension>
<Photo>EmpID6.bmp</Photo>
<ReportsTo>5</ReportsTo>
</Employees>
- <Employees>
<EmployeeID>7</EmployeeID>
<LastName>King</LastName>
<FirstName>Robert</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Mr.</TitleOfCourtesy>
<BirthDate>1960-05-29T00:00:00</BirthDate>
<HireDate>1994-01-02T00:00:00</HireDate>
<Address>Edgeham Hollow Winchester Way</Address>
<City>London</City>
<PostalCode>RG1 9SP</PostalCode>
<Country>UK</Country>
<HomePhone>(71) 555-5598</HomePhone>
<Extension>465</Extension>
<Photo>EmpID7.bmp</Photo>
<ReportsTo>5</ReportsTo>
</Employees>
- <Employees>
<EmployeeID>8</EmployeeID>
<LastName>Callahan</LastName>
<FirstName>Laura</FirstName>
<Title>Inside Sales Coordinator</Title>
<TitleOfCourtesy>Ms.</TitleOfCourtesy>
<BirthDate>1958-01-09T00:00:00</BirthDate>
<HireDate>1994-03-05T00:00:00</HireDate>
<Address>4726 - 11th Ave. N.E.</Address>
<City>Seattle</City>
<Region>WA</Region>
<PostalCode>98105</PostalCode>
<Country>USA</Country>
<HomePhone>(206) 555-1189</HomePhone>
<Extension>2344</Extension>
<Photo>EmpID8.bmp</Photo>
<ReportsTo>2</ReportsTo>
</Employees>
- <Employees>
<EmployeeID>9</EmployeeID>
<LastName>Dodsworth</LastName>
<FirstName>Anne</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Ms.</TitleOfCourtesy>
<BirthDate>1969-07-02T00:00:00</BirthDate>
<HireDate>1994-11-15T00:00:00</HireDate>
<Address>7 Houndstooth Rd.</Address>
<City>London</City>
<PostalCode>WG2 7LT</PostalCode>
<Country>UK</Country>
<HomePhone>(71) 555-4444</HomePhone>
<Extension>452</Extension>
<Photo>EmpID9.bmp</Photo>
<ReportsTo>5</ReportsTo>
</Employees>
</dataroot>
 
J

John Nurick

You haven't opened or created a database. Without that, there's nowhere
for the table to be created.
 
N

Nevie and Phil

I thought it went with the currentdb, how do i open or create a database, can
I do it for the current database??

Thanks much!
 
J

John Nurick

Your code reads as if you want to run it in an application other than
Access. This line
Set appAccess = CreateObject("Access.Application")
creates a new instance of Access (a new Access Application object) with
no current database. Then
appAccess.ImportXML ...
attempts to import data into that nonexistent current database.

Assuming your code is running in an Access application and there is a
current database, forget about appAccess and try just
Application.ImportXML ...
 
N

Nevie and Phil

Wow! that was so easy!!!! Thanks for the help!!! Your the man!

I didn't know you could just used Application... that's great :)

Thank you
Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top