ImportXML doesn't give me a table

[Guest]

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 xmlnsd="urn:schemas-microsoft-comfficedata">
- <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>

 

[John Nurick]

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

 

[Guest]

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!

 

[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 ...

 

[Guest]

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