Re: transfer data into an external database

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



On Wed, 1 Jun 2005 08:41:06 -0700, "Alan UK"
<alanuk@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Relevant Pages

  • Re: transfer data into an external database
    ... >With regards to your SQL code I have no idea about SQL. ... If there is a schema.ini in the same folder as the CSV file, ... >> Create a Schema.ini file based on an existing table in your database: ... John Nurick ...
    (microsoft.public.access.externaldata)
  • Re: Pre-set Query Conditions that cannot be changed
    ... to a CSV file and then convert that CSV file in Word to a table, ... that 'Query Options have been set', but trying to change them reveals ... at a time into a blank database. ... methods required re-defintion of fields and reports. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Pre-set Query Conditions that cannot be changed
    ... they have found out how to unlock the hidden Query Options. ... the Works database is incompatible with Word and that some interoperability ... to a CSV file and then convert that CSV file in Word to a table, ... Word MVP web site http://word.mvps.org ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Exporting table to .csv file, then importing .csv file back to
    ... "John Nurick" wrote: ... Option Database ... Public Sub ReImportFromTheCSVFile() ... I have used the wizard within MS Access 2000 to export to a csv file, ...
    (microsoft.public.access.externaldata)
  • Re: PHP/MySQL project
    ... > Looking to build a dynamic site using PHP/MySQL. ... > import changes to the database easily. ... Import the csv file into the MySql db, and then use phpmyadmin in the future ... The version of PHP will be important if you are considering finding / buying ...
    (alt.php)
Loading