Re: transfer data into an external database
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Thu, 02 Jun 2005 06:19:26 +0100
On Wed, 1 Jun 2005 12:37:12 -0700, "Alan UK"
<alanuk@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>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? ;-)
>"John Nurick" wrote:
>
>> 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.
>>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- References:
- transfer data into an external database
- From: Alan UK
- Re: transfer data into an external database
- From: John Nurick
- Re: transfer data into an external database
- From: Alan UK
- transfer data into an external database
- Prev by Date: RE: Replace existing records in a table
- Next by Date: Re: How to avoid connection popup?
- Previous by thread: Re: transfer data into an external database
- Next by thread: Re: How to avoid connection popup?
- Index(es):
Relevant Pages
|
