Need Help - How to do a "make table query" by code

  • Thread starter Thread starter FatMan
  • Start date Start date
F

FatMan

Hi all:
What I would like to know is what is the code to do a "make table query" by
using code and the query by example grid. Can someone please help me?

Thanks,
FatMan
 
Hi all:
What I would like to know is what is the code to do a "make table query" by
using code and the query by example grid. Can someone please help me?

Thanks,
FatMan
Click to expand...

You seem to want two different things... use code and the query grid.

Why not give us a bit more help on what it is you wish to accomplish.
Sometimes a few examples helps. Then perhaps someone can show you the
way. Also tell us your Access version number.
 
Fred:
Thanks for your reply.

O.K. as for more detail:

Access version: Access 2000

Goal: What I would like to do through code is make a copy of a table in
my database and save it under a different name.

I thought the easiest way to do this was with code that would emulate the
“make table query†functionality found in the “query by example†part of
access.

Any help is greatly appreciated,
FatMan
 
One line of VBA code.

DoCmd.CopyObject ,"NewTableName",acTable,"OldTableName"

Note the leading comma is needed. The first argument is destination database
(as a string), but if you leave it blank then it select the current database.
If that bothers you, you can use

docmd.CopyObject CurrentDb().Name,"FAQCopy",acTable,"FAQ"

Or type in the path and name of the database that is the target for copying
the table to. Check out the help for Copy Object for a more comprehensive
discussion.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John:
Thank you, your suggestion worked like a charm.

Can you tell me if it is possible using code in one database to delete the
contents of table stored in another database?

I know the above questions must sound funny but please allow me to explain
so you know it is legit. What I am doing is using an access database to
complete our year-end inventory count. To do this we use Symblol hand held
scanners to capture the stock codes and quantities. The company we purchased
the scanners from have written code that will APPEND the data from the
scanners to an access database (Access 2000) table called CollectedData.
What I do in my database is import their table, copy it and then append the
records to my table used to capture the count.

Since their code APPENDS the data to their table there is a chance for
duplication. So what I would like to do is after I have imported, copied and
appended the data in my database is to delete the contents of their
CollectedData table.

Is this possible and if so what would the code be?

Once again your help is appreciated.

Thanks,
FatName
 
John:
Thank you, your suggestion worked like a charm.

Can you tell me if it is possible using code in one database to delete the
contents of table stored in another database?

I know the above questions must sound funny but please allow me to explain
so you know it is legit. What I am doing is using an access database to
complete our year-end inventory count. To do this we use Symblol hand held
scanners to capture the stock codes and quantities. The company we purchased
the scanners from have written code that will APPEND the data from the
scanners to an access database (Access 2000) table called CollectedData.
What I do in my database is import their table, copy it and then append the
records to my table used to capture the count.

Since their code APPENDS the data to their table there is a chance for
duplication. So what I would like to do is after I have imported, copied and
appended the data in my database is to delete the contents of their
CollectedData table.

Is this possible and if so what would the code be?

Once again your help is appreciated.

Thanks,
FatName
Click to expand...

Here is one way.

Public Sub DeleteForeignData()
On Error GoTo Err_Handler
Dim Db As DAO.Database
Set Db = OpenDatabase("c:\YourPath\YourOtherDatabase.mdb")

Db.Execute "Delete * from YourTableName", dbFailOnError

Exit_Sub:
Db.Close
Set Db = Nothing
Exit Sub
Err_Handler:
MsgBox "Error #: " & Err.Number & " " & Err.Description
Resume Exit_Sub
End Sub
 
Fred:
Thanks for your post.

So far all my code has been done in "Private Sub" and not in a "Public Sub".
Can this code be included in my "Private Sub" at the end of my existing
code. It is the next logical step in my processing of the data/file.

If it does have to be a "Public Sub" how do I go about creating a "public
sub" and then how do I call it from my "Private Sub"?

I use VBA from Access 2000.

Any and all help is greatly appreciated.

Thanks,
FatMan
 
Fred:
Thanks for your post.

So far all my code has been done in "Private Sub" and not in a "Public Sub".
Can this code be included in my "Private Sub" at the end of my existing
code. It is the next logical step in my processing of the data/file.

If it does have to be a "Public Sub" how do I go about creating a "public
sub" and then how do I call it from my "Private Sub"?

I use VBA from Access 2000.

Any and all help is greatly appreciated.

Thanks,
FatMan
Click to expand...

You can place the Sub, just as it is written, anywhere in your
database, i.e. in a form's class code window, or in a Module.
My naming it Public, it can be called from anywhere in the database.

You would then call the function from any code event, using

Private Sub SomeEventNameHere()
'Do some stuff here
DeleteForeignData
' Do some other stuff here if necessary
End Sub
 
Back
Top