Please Help! Reference another DB in code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to import all my day's data in the app that takes in the most data.
How can I have certain spreadsheets import into a table in another db?
Should I link the table into the DB doing the importing, even tho I'll never
use it in that DB except for importing data?

Code:
strImportFileName = Format([SaleDate], "mm-dd-yy") & " POS6A" & ".xls"

DoCmd.TransferSpreadsheet acImport, 8, "T: Hourly Data by Store", _
"C:\TestAutoImport\" & strImportFileName, True, ""

Where "T: Hourly Data by Store" is in an external application called
"HourlyDataTables.mdb".

Thanks
sara
 
Hi Sara,

If you're running this code in your front end mdb, then yes, a linked
table is probably the simplest way to go. Alternatively, you can build
up an SQL statement using syntax like this to reference the workbook,
and an IN clause to specify that the destination is an external .mdb.

INSERT *
INTO
[TableName] IN "C:\Folder\Filename.mdb"
FROM
[Excel 8.0;HDR=Yes;Database=C:\Temp\Workbook.xls;].[Sheet1$]
;

By the way, in the long run life is much easier if you never use spaces
in the names of tables, field, etc, - and never never use special
characters such as colons.


I want to import all my day's data in the app that takes in the most data.
How can I have certain spreadsheets import into a table in another db?
Should I link the table into the DB doing the importing, even tho I'll never
use it in that DB except for importing data?

Code:
strImportFileName = Format([SaleDate], "mm-dd-yy") & " POS6A" & ".xls"

DoCmd.TransferSpreadsheet acImport, 8, "T: Hourly Data by Store", _
"C:\TestAutoImport\" & strImportFileName, True, ""

Where "T: Hourly Data by Store" is in an external application called
"HourlyDataTables.mdb".

Thanks
sara
Click to expand...
 
Back
Top