make query a table

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

Guest

I use some application that when retrieving Access database, it only
recognize tables, but not queries.

However, I need to use this query I created. If I create a new database and
import the query, it still becomes a query in the new database. Currently, I
save the query as Excel file, then import the Excel into the new database.
But later on, I will need to process about 2 million records. I don't think
Excel can take that many data.

How do I convert the query into a table in Access directly?
 
YuChieh said:
I use some application that when retrieving Access database, it only
recognize tables, but not queries.

However, I need to use this query I created. If I create a new
database and import the query, it still becomes a query in the new
database. Currently, I save the query as Excel file, then import the
Excel into the new database. But later on, I will need to process
about 2 million records. I don't think Excel can take that many data.

How do I convert the query into a table in Access directly?
Click to expand...

It seems you may have more problems than just this one with your design,
but for you specific question, just change the query type to make table
query and you can make a new table in that database based on the query.

It sounds as thou you may want to LINK from one database to another
rather than move the actual data. If you move the data, you will never see
any updates in the new database unless you do another import. If you like
the second database will always be up to date.
 
YuChieh said:
I use some application that when retrieving Access database, it only
recognize tables, but not queries.
Click to expand...

That's a flaw in that application, but I guess you can't do anything
about that.
However, I need to use this query I created. If I create a new
database and import the query, it still becomes a query in the new
database. Currently, I save the query as Excel file, then import the
Excel into the new database. But later on, I will need to process
about 2 million records. I don't think Excel can take that many data.

How do I convert the query into a table in Access directly?
Click to expand...

You can import a query as a table. If you import via the user interface
(File -> Get External Data -> Import...), click the Options button on
the import dialog and choose to import queries as Tables, not as
Queries. Using code (the TransferDatabase method), you can also export
a query from one database into a table in another database.

Two million records is a lot of records! Will they all fit in an Access
..mdb file, which has a 2GB size limit?
 
Actually, after the query is done, nothing will be changed any more. Design
is fine. It's the other application (variable printing software) that is not
sophisticated to see the queries in Access. oh well...

Yes, you answered my question. I tried, changing query type to make table
query. It works the way I wanted.


Thanks
 
I have now 30,000 records, file size is about 77,000 KB (77MB). So 2
million, estimate size will be over 5 GB? (if I calculated right). I guess
it will exceed the limite in Access.
:( maybe I should try in SQL?

I didn't see the Option tab in import. it works. Thanks.
 
YuChieh said:
I have now 30,000 records, file size is about 77,000 KB (77MB). So 2
million, estimate size will be over 5 GB? (if I calculated right).
I guess it will exceed the limite in Access.
:( maybe I should try in SQL?
Click to expand...

If 77MB is the *compacted* size with 30,000 records, then I think you'd
better be looking at another data store than a Jet database. SQL Server
would be an option.
 
Dirk Goldgar said:
Two million records is a lot of records! Will they all fit in an Access
..mdb file, which has a 2GB size limit?
Click to expand...

I have an app (in Access 97 no less!) that has 3 tables with about 800,000
records with 10-15 fields each. It requires frequent compacting, (which is
time consuming) but it all comes in at a nice sleek 650 MBs. So it can be
done - of course, you need to be stingy about EVERYTHING - i.e. none of this
255 character text data fields stuff!
 
YuChieh said:
I have now 30,000 records, file size is about 77,000 KB (77MB). So 2
million, estimate size will be over 5 GB? (if I calculated right).
I guess it will exceed the limite in Access.
:( maybe I should try in SQL?

I didn't see the Option tab in import. it works. Thanks.
Click to expand...

Maybe, but keep in mind that you need to compact it to make it smaller
and also keep in mind that all the reports forms queries etc, don't get any
larger with more records. Try doing a compact and see what happens.
 
YuChieh said:
How do you mean "compacting"? What do I need to do?
Click to expand...

Top line Menu "Tools" then "Database Utilities" Then Compact

As you use an Access database, most people end up deleting some data.
Access, like many databases does not actually delete it, but just marks it
as deleted so you no longer see it, but it is still there until you compact
it. You may have a much smaller database than you think once you do a
compact.
 
Hi,

I developed a personal utility to automatically backup, compact and restore
my clients Access 97 databases several years ago. It was to solve the
issues of IT failing to do what they claimed they were doing. They claimed
they did a new backup every night. What they didn't reveal was that last
night's backup was the only backup of the applications kept. Older files
were overwritten.

If you want to give it a try, decode my address and send an email requesting
a copy of DBAX.. The first caveat is to always do your testing and playing
on a backup copy of your application, or use a test application until you're
sure of it.

It will require that the front End(s) of your original application open an
invisible form and leave it open. The one and only copy of DBAX is called
in the Close event of that form, which will be the last thing to happen as
your application closes. DBAX allows the naming of target files to compact
and target storage folders for the backups. It will create a target folder
for each day of the week as it is needed - with a warning prompt. The
second backup on a Wednesday will overwrite the previous backup from a
Wednesday. Every time a FE closes a compact will be attempted. If there is
still a user in the application the compact will quietly fail. Eventually,
the closing FE will be the last one and DBAX will run to completion.

It will keep the DBAX application running on the last workstation to close
the calling application until the file machinations are done. It means that
ho one has to remember to do the compact. DBAX will self extinguish when
done. It also provides you with a fresh backup of your data every day you
run your application.
 
Back
Top