Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make Table Query (A2k)

    I need some syntax help please with the following query:

    <pre>SELECT DETLAB.* INTO tblDETLAB FROM DETLAB;</pre>


    I need it to specify:

    <pre>SELECT DETLAB.* INTO tblDETLAB FROM DETLAB;</pre>

    Where EST_NO > 50000

    Any help would be apprecated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (A2k)

    Hi Dave,

    The following should work:

    SELECT DETLAB.* INTO tblDETLAB FROM DETLAB WHERE DETLAB.EST_NO > 50000

    (Don't forget to remove the ; after FROM DETLAB)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (A2k)

    Thanks Hans, I tried this and nothing seems to be wrong with your version, but am still having problems importing the data.

    I'll have to take my laptop to work tomorrow with the backup on.

    I'll keep you posted.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (A2k)

    Hans
    I need to watch whats happening with this query.
    It seems to progress so far then hangs, no error msg's etc.

    is this possible ??

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (A2k)

    What is DETLAB? A table in the Access database, or a linked external table? If the latter, what kind of link? (ODBC, ...)

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (A2k)

    Yes Hans, an external table linked via ODBC

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (A2k)

    One workaround would be to import the table instead of linking it.

    If you'd rather work with a linked table, you can turn on ODBC tracing in the ODBC Data Source Administrator control panel. In the Tracing tab, you can specify a log file, and click Start Tracing Now.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (A2k)

    Hans

    I have been wary over importing, in case I mess something up in the original source.
    But, if you feel it's safe to do so, then I will try.

    Thanks again

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (A2k)

    Hans

    I'm still having problems importing data.
    Certain tables import ok where as a couple of large tables seem to hang (Make Table Query) .

    I seem to have invoked an error though which might give an indication of whats going wrong.
    I copied the source table "PRT.DAT" and changed it to "PRT.TXT". From this I tried importing again (TXT Delim...) which hung and stated no more than 65000 records could be imported.
    Considering we are on Estimate No 64000..ish, the count seems true.

    Do you have any other idea's which you think could work ?
    At least I had an error.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table Query (A2k)

    Dave,

    I've never tried importing text files with that many records. You could try splitting the text file into chunks of, say, 40,000 records. You shouldn't have problems importing or perhaps linking those, and you can certainly append many more than 65,000 records to an Access table.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make Table Query (A2k)

    I think that may be a function of the 2000 import code - as Hans indicates, you can certainly import more records than that if you break it into chunks. I just imported 345K records into one table, and another 2.54M records into a related table with a foreign key (my DataFlex project I chatted with you about a few weeks ago). In those cases the files were broken into 30K chunks for the headers and 100K chunks for the details - however I was using Access 2003.
    Wendell

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Query (A2k)

    Wendell

    Thanks for the reply.
    Can you give me any instructions on how to break the data into chunks ?

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make Table Query (A2k)

    I was using the DataFlex tool DFQuery - in that I could limit the number of records, in fact I had to because it only allocates an 8MByte buffer, and once you fill it up, it wraps around and overwrites the first part of the data. The tables I was dumping to comma-delimited format had a record number that I could set constraints on, so we used that. In one case I could only dump 30K records at a shot, but in the table with fewer fields I could dump 100K at a shot - still it was 26 text files which I then had to import one at a time to recreate the 2.54M record table.

    In your case if you get the whole file in one big chunk, you might look at opening it in Word or another text editor, deleting a portion of the file, and resaving it so you end up with several text files - the only challenge is to make sure you don't drop any data or include it in two files.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •