transfer data into an external database

[Alan UK]

I have a database that contains large tables (BigTables.mdb) and a module to
import a csv file using an import specification.
I want to run this module and populate this database by running code in a
different database (Control.mdb) where the queries on the tables sit. To be
clear I want to refresh BigTables.mdb with the csv file, not Control.mdb
I understand the code that calls queries in the external database using DAO,
and have tried to call the module using a similar technique and placing a
reference to BigTables.mdb into Control.mdb

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase _
("D:\BigTables.mdb")
BigTables.[test2].Importatron1
Set db = Nothing

Unfortunately this runs the import into Control.mdb, so I get a new and
unwanted table there instead of in BigTables.mdb.
Can someone please tell me where this is going wrong, or if another method
is more appropriate?

Thanks for your assistance

[*Please* don't refer me to any help files on this matter, however, as
neither my machine at home nor at work seems to be able to access these help
files. I have checked
MS DAO 3.6 Object Library and
MS ADO Ext. 2.5 DLL for Security
in the object library references - is there something else that needs a
check before this will work or the help files are viewable for related topics?
I apologise that this was also posted elsewhere, but failed to get a
response that helped]

 

[John Nurick]

Hi Alan,

As the Irishman said, I wouldn't start from there if I were you. Instead
I'd either

- set up a linked table in Control.mdb connected to the table in
BigTables, and also move the import module and import specification from
BigTables.mdb into Control.mdb. The code in the module might need
revising.

- do the import by executing a query in Control.mdb. The query would
look something like this:

INSERT INTO MyTable IN D:\Folder\BigTables.mdb
SELECT *
FROM [Text;HDR=YES;Database=D:\MyFolder\;].MyFile#txt

If the import specification is really necessary (often they aren't with
CSV files) I'd replace it with a schema.ini file in the same folder as
the CSV file. Schema.ini is documented towards the end of the help
article "Initializing the Text Data Source Driver" in Microsoft Jet SQL
help. See also:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

I have a database that contains large tables (BigTables.mdb) and a module to
import a csv file using an import specification.
I want to run this module and populate this database by running code in a
different database (Control.mdb) where the queries on the tables sit. To be
clear I want to refresh BigTables.mdb with the csv file, not Control.mdb
I understand the code that calls queries in the external database using DAO,
and have tried to call the module using a similar technique and placing a
reference to BigTables.mdb into Control.mdb

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase _
("D:\BigTables.mdb")
BigTables.[test2].Importatron1
Set db = Nothing

Unfortunately this runs the import into Control.mdb, so I get a new and
unwanted table there instead of in BigTables.mdb.
Can someone please tell me where this is going wrong, or if another method
is more appropriate?

Thanks for your assistance

[*Please* don't refer me to any help files on this matter, however, as
neither my machine at home nor at work seems to be able to access these help
files. I have checked
MS DAO 3.6 Object Library and
MS ADO Ext. 2.5 DLL for Security
in the object library references - is there something else that needs a
check before this will work or the help files are viewable for related topics?
I apologise that this was also posted elsewhere, but failed to get a
response that helped]

 

[Alan UK]

Thanks so much John -BUT !

I do need an equivalent to import specs as there are some fields that
importing flat will drop leading zeros, so it has to be defined as text to
prevent the auto recognition as an integer.
The table will be linked to in Control.mdb, but it needs to sit in
BigTables.mdb. as the queries run against it will mushroom the database size
quite sufficently without extra tables!

With regards to your SQL code I have no idea about SQL. If I were running
your code how would I reference the schema.ini file, and what would I call it
? Luckily the table/csv this relates to has only about 6 columns so I'd write
the file manually in Notebook I would imagine. This, as you may have guessed,
will not be limited to a single table, however, there are about 3 or 4 that
require to be handled and refreshed in the same way.

SQL "INSERT" command also produces an error in my Access help. What object
library am I missing there, I wonder?

*sigh* one day I'll try and do something I know how to do...but where would
be the fun and the acheivement in that? ;-)

Hi Alan,

As the Irishman said, I wouldn't start from there if I were you. Instead
I'd either

- set up a linked table in Control.mdb connected to the table in
BigTables, and also move the import module and import specification from
BigTables.mdb into Control.mdb. The code in the module might need
revising.

- do the import by executing a query in Control.mdb. The query would
look something like this:

INSERT INTO MyTable IN D:\Folder\BigTables.mdb
SELECT *
FROM [Text;HDR=YES;Database=D:\MyFolder\;].MyFile#txt

If the import specification is really necessary (often they aren't with
CSV files) I'd replace it with a schema.ini file in the same folder as
the CSV file. Schema.ini is documented towards the end of the help
article "Initializing the Text Data Source Driver" in Microsoft Jet SQL
help. See also:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

I have a database that contains large tables (BigTables.mdb) and a module to
import a csv file using an import specification.
I want to run this module and populate this database by running code in a
different database (Control.mdb) where the queries on the tables sit. To be
clear I want to refresh BigTables.mdb with the csv file, not Control.mdb
I understand the code that calls queries in the external database using DAO,
and have tried to call the module using a similar technique and placing a
reference to BigTables.mdb into Control.mdb

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase _
("D:\BigTables.mdb")
BigTables.[test2].Importatron1
Set db = Nothing

Unfortunately this runs the import into Control.mdb, so I get a new and
unwanted table there instead of in BigTables.mdb.
Can someone please tell me where this is going wrong, or if another method
is more appropriate?

Thanks for your assistance

[*Please* don't refer me to any help files on this matter, however, as
neither my machine at home nor at work seems to be able to access these help
files. I have checked
MS DAO 3.6 Object Library and
MS ADO Ext. 2.5 DLL for Security
in the object library references - is there something else that needs a
check before this will work or the help files are viewable for related topics?
I apologise that this was also posted elsewhere, but failed to get a
response that helped]

 

[John Nurick]

Thanks so much John -BUT !

I do need an equivalent to import specs as there are some fields that
importing flat will drop leading zeros, so it has to be defined as text to
prevent the auto recognition as an integer.
The table will be linked to in Control.mdb, but it needs to sit in
BigTables.mdb. as the queries run against it will mushroom the database size
quite sufficently without extra tables!

If you have a linked table in Control.mdb anyway, just move your import
code from the module in BigTables to one in Control.mdb. Data you import
to a linked table goes straight to the "real" table and won't bloat
Control.mdb.

(By the way, if you're using temporary tables in Control.mdb and bloat
is a problem, a standard technique is to write code that creates a
temporary .mdb file just to hold the temporary tables. At the end of the
session, just delete the temporary .mdb.

With regards to your SQL code I have no idea about SQL. If I were running
your code how would I reference the schema.ini file, and what would I call it

Because you've got linked tables in the front end (Control.mdb) you
don't need to do it this way....

If there is a schema.ini in the same folder as the CSV file, the Jet
text driver will use it automatically. You don't have to do anything in
the SQL or the way you execute it. Schema.ini must have a section with
the same name as the CSV file, i.e. if you have multiple files to import
you either have a schema.ini with one section for each, or else have
your code create the appropriate schema.ini each time.

? Luckily the table/csv this relates to has only about 6 columns so I'd write
the file manually in Notebook I would imagine. This, as you may have guessed,
will not be limited to a single table, however, there are about 3 or 4 that
require to be handled and refreshed in the same way.

SQL "INSERT" command also produces an error in my Access help. What object
library am I missing there, I wonder?

The way to get to this is via the contents page: look for Microsoft Jet
SQL Reference, Data Manipulation Language.

*sigh* one day I'll try and do something I know how to do...but where would
be the fun and the acheivement in that? ;-)

Hi Alan,

As the Irishman said, I wouldn't start from there if I were you. Instead
I'd either

- set up a linked table in Control.mdb connected to the table in
BigTables, and also move the import module and import specification from
BigTables.mdb into Control.mdb. The code in the module might need
revising.

- do the import by executing a query in Control.mdb. The query would
look something like this:

INSERT INTO MyTable IN D:\Folder\BigTables.mdb
SELECT *
FROM [Text;HDR=YES;Database=D:\MyFolder\;].MyFile#txt

If the import specification is really necessary (often they aren't with
CSV files) I'd replace it with a schema.ini file in the same folder as
the CSV file. Schema.ini is documented towards the end of the help
article "Initializing the Text Data Source Driver" in Microsoft Jet SQL
help. See also:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

I have a database that contains large tables (BigTables.mdb) and a module to
import a csv file using an import specification.
I want to run this module and populate this database by running code in a
different database (Control.mdb) where the queries on the tables sit. To be
clear I want to refresh BigTables.mdb with the csv file, not Control.mdb
I understand the code that calls queries in the external database using DAO,
and have tried to call the module using a similar technique and placing a
reference to BigTables.mdb into Control.mdb

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase _
("D:\BigTables.mdb")
BigTables.[test2].Importatron1
Set db = Nothing

Unfortunately this runs the import into Control.mdb, so I get a new and
unwanted table there instead of in BigTables.mdb.
Can someone please tell me where this is going wrong, or if another method
is more appropriate?

Thanks for your assistance

[*Please* don't refer me to any help files on this matter, however, as
neither my machine at home nor at work seems to be able to access these help
files. I have checked
MS DAO 3.6 Object Library and
MS ADO Ext. 2.5 DLL for Security
in the object library references - is there something else that needs a
check before this will work or the help files are viewable for related topics?
I apologise that this was also posted elsewhere, but failed to get a
response that helped]