determining newly created twin tables following import of xml data

[Guest]

Hi

I can successfully import a series of xml files one at a time. I have been
using the method Application.ImportXML to achieve this.

Because of the structure of each file, each import creates two tables. The
table names have a format like:

qryPurchOrdersAndSuppliers(n)
LineItem(n)

where n represents either nothing or a numeric.

eg qryPurchOrdersAndSuppliers or qryPurchOrdersAndSuppliers1 or
qryPurchOrdersAndSuppliers2 etc

I want to append the data from each table into another table.
eg qryPurchOrdersAndSuppliers(n) into tblSales and LineItem(n) into
tblSaleDetails.

I have discovered that if I delete the tables qryPurchOrdersAndSuppliers and
LineItem, then the next import will reuse those names.

Note though in practice there might be imports from several users at near
simultaneous times, therefore the two tables (qryPurchOrdersAndSuppliers and
LineItem) might not be deleted before the next import by somebody else and
therefore the issue of the numeric being appended at the end comes into play.

So, the problem I have is that I cannot be sure of the exact names of the
two tables that will be created following the import.

Is there any way of determining the precise name of the two tables that have
been created?

Kind regards

Ross Petersen

 

['69 Camaro]

Hi, Ross.

Is there any way of determining the precise name of the two tables that
have
been created?

Yes. Create a temporary database file and import the two tables into that
file. _All_ of the user-defined tables in that file are the tables that
were just imported, so just iterate through the temporary database's Tables
Collection to get the names. Use a remote query to append the data from
these tables into tblSales and tblSaleDetails. When finished, delete the
temporary database file. The extra benefit is that your database doesn't
bloat needlessly with temporary tables.

To create a unique temporary file name, copy the code from the following Web
page and paste it into a standard module:

http://www.mvps.org/access/api/api0014.htm

Add the following function to change the temporary file name to an MDB file
name:

Public Function getTempDBName() As String

On Error GoTo ErrHandler

Dim sTempDB As String

sTempDB = TempFile(False, , CurrentProject.Path)
getTempDBName = Mid$(sTempDB, 1, Len(sTempDB) - 3) & "mdb"

Exit Function

ErrHandler:

MsgBox "Error in getTempDBName( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Function

Use the value returned by getTempDBName( ) to name the path and file name of
the temporary database file created with the CreateDatabase method.

After the XML table import, iterate through the user-defined tables in the
temporary database to determine the names (which probably don't have any
number suffixes) and plug those into your remote query. Here's an example
of a remote query which appends data from the remote database table,
qryPurchOrdersAndSuppliers, into a local table, tblSales:

INSERT INTO tblSales
SELECT *
FROM [;DATABASE=C:\tmpB4E.mdb].qryPurchOrdersAndSuppliers;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.