Create an import holding table in a temporary database

[robert d via AccessMonster.com]

I currently use the TransferSpreadsheet function for creating the table
needed to hold the imported Excel spreadsheet data. Since the holding table
doesn't exist the function will create it. This frees me from having to know
how many fields to create, etc.

However, now I want this holding table to be created in a temporary database
so that I can free my front end from the bloat that's caused by the import of
the data.

Is there a way I can create this table in the temporary database while still
not having to specify the structure of the table. This table is only a
holding table and will be killed after the import, so I can't see spending
the time to create a lot of code for specifying its structure, if I don't
have to.

So the current code is:

DoCmd.TransferSpreadsheet acImport, , mTable, mFile

Is there someway to include the path with the table name (currently in mTable)
.. Currently mTable is only the table name.

Thanks.

 

[robert d via AccessMonster.com]

To answer my own post:

How about if I add some code to the modeltempDB which code essentially
performs the TransferSpreadsheet function, including the creation of the
import holding table (which would now be by default created in the tempDB).
Then code returns to the front end to link the new table and continue.

So:

1) In FE do all the necessary things to prepare for the import
2) Call procedure in tempDB which has TransferSpreadsheet. Immediately after
this is done return to the front end code.
3) Link to the new import holding table and continue with processing.

If this is viable, how would I call the procedure in the tempDB from the
front end?

 

[John Nurick]

Hi Robert,

It's much simpler to run a make-table query in the main MDB to create
the table in the temp MDB from the Excel sheet, and then use
DoCmd.TransferDatabase acLink to link to the new table. That way, all
your code is running in the main MDB.

The SQL statement for the make-table query will look something like
this:

SELECT *
INTO NewTable IN 'C:\Folder\Temp.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\Folder\File.xls;].[Sheet1$]
;

Presumably you'll need to cater for changing names and locations of the
temporary MDB and the Excel document. I'd do that by writing VBA code to
build the appropriate SQL in a string variable (e.g. strSQL), and then
using
CurrentDB.Execute strSQL, dbFailOnError
to execute it.

There's sample code for creating and using temporary MDB files at
http://www.tek-tips.com/faqs.cfm?fid=5980

 

[robert d via AccessMonster.com]

I need some help with the following make table SQL:

strSQL = "SELECT * INTO '" & mTable & "' IN '" & Forms!StartAppForm!
TempDBName & "' FROM [Excel 8.0;HDR=Yes;database = ImportFile;].SheetNumber"

CurrentDb.Execute strSQL, dbFailOnError

I'm trying to create a table (mTable) in my temporary database (the name is
in a textbox on the StartAppForm) from an Excel spreadsheet. The error is in
the FROM [Excel....] part. The error is not because I haven't included the
concatenation operator in the above statements as shown (in my code it is one
long line, but I can never remember what the character string is when I post
here in the forum).

The problem probably has something to do with the ImportFile;].SheetNumber
syntax. Both ImportFil and SheetNumber are textboxes on the form that contain
the strings. I'm sure I need to concatenate these as I did with mTable, but
the ; and ] are confusing me.

Thanks.

Hi Robert,

It's much simpler to run a make-table query in the main MDB to create
the table in the temp MDB from the Excel sheet, and then use
DoCmd.TransferDatabase acLink to link to the new table. That way, all
your code is running in the main MDB.

The SQL statement for the make-table query will look something like
this:

SELECT *
INTO NewTable IN 'C:\Folder\Temp.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\Folder\File.xls;].[Sheet1$]
;

Presumably you'll need to cater for changing names and locations of the
temporary MDB and the Excel document. I'd do that by writing VBA code to
build the appropriate SQL in a string variable (e.g. strSQL), and then
using
CurrentDB.Execute strSQL, dbFailOnError
to execute it.

There's sample code for creating and using temporary MDB files at
http://www.tek-tips.com/faqs.cfm?fid=5980

To answer my own post:

[quoted text clipped - 36 lines]

 

[John Nurick]

You need to concatenate the other items into the string in the same way
as you do the textbox with the TempDBName, along the lines of

strSQL = "SELECT * INTO '" & mTable & vbCrLf _
& "' IN '" & Forms!StartAppForm!TempDBName & vbCrLf _
& "' FROM [Excel 8.0;HDR=Yes;Database=" & Me!ImportFile _
& ";]." & Me!SheetNumber & ";"

I need some help with the following make table SQL:

strSQL = "SELECT * INTO '" & mTable & "' IN '" & Forms!StartAppForm!
TempDBName & "' FROM [Excel 8.0;HDR=Yes;database = ImportFile;].SheetNumber"

CurrentDb.Execute strSQL, dbFailOnError

I'm trying to create a table (mTable) in my temporary database (the name is
in a textbox on the StartAppForm) from an Excel spreadsheet. The error is in
the FROM [Excel....] part. The error is not because I haven't included the
concatenation operator in the above statements as shown (in my code it is one
long line, but I can never remember what the character string is when I post
here in the forum).

The problem probably has something to do with the ImportFile;].SheetNumber
syntax. Both ImportFil and SheetNumber are textboxes on the form that contain
the strings. I'm sure I need to concatenate these as I did with mTable, but
the ; and ] are confusing me.

Thanks.

Hi Robert,

It's much simpler to run a make-table query in the main MDB to create
the table in the temp MDB from the Excel sheet, and then use
DoCmd.TransferDatabase acLink to link to the new table. That way, all
your code is running in the main MDB.

The SQL statement for the make-table query will look something like
this:

SELECT *
INTO NewTable IN 'C:\Folder\Temp.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\Folder\File.xls;].[Sheet1$]
;

Presumably you'll need to cater for changing names and locations of the
temporary MDB and the Excel document. I'd do that by writing VBA code to
build the appropriate SQL in a string variable (e.g. strSQL), and then
using
CurrentDB.Execute strSQL, dbFailOnError
to execute it.

There's sample code for creating and using temporary MDB files at
http://www.tek-tips.com/faqs.cfm?fid=5980

To answer my own post:

[quoted text clipped - 36 lines]

Thanks.