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

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
 
F

fredg

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.
 
F

FatMan

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
 
J

John Spencer

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
 
F

FatMan

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
 
F

fredg

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
 
F

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
 
F

fredg

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
 
F

FatMan

Fred:
Thanks for the help. I have done as you suggested and have cut and pasted
your code after the "end sub" statement of the event procure that does all my
processing. I have also added the line to call the DeleteForiengData
procedure.

As I have mentioned before I am using Access 2000. When I run/test the code
now I get the following error:

Compile error:
User-defined type not defined

I believe this error code is coming up when it tries to run the line:
Dim Db As DAO.Database

Any ideas what might be causing the error message?

Your help is appreciated as much as it is needed....lots!

Thanks,
FatMan
 
F

fredg

Fred:
Thanks for the help. I have done as you suggested and have cut and pasted
your code after the "end sub" statement of the event procure that does all my
processing. I have also added the line to call the DeleteForiengData
procedure.

As I have mentioned before I am using Access 2000. When I run/test the code
now I get the following error:

Compile error:
User-defined type not defined

I believe this error code is coming up when it tries to run the line:
Dim Db As DAO.Database

Any ideas what might be causing the error message?

Your help is appreciated as much as it is needed....lots!

Thanks,
FatMan
Click to expand...

Open any code module in Design view (or click Ctrl + G).
On the Tools menu, click References.
*** Make sure there is a reference set to the
Microsoft DAO 3.6 Object Library
If there isn't, scroll down the list and place a check mark next to
that reference.
Exit the dialog.

Also watch your spelling
Your "DeleteForiengData" is not the same as my
"DeleteForeignData".
The actual spelling doesn't matter, as long as it is consistent, both
in the Sub and when you call it.
 
F

FatMan

Fred:
Thanks for the help. Once I added the Microsoft DAO 3.6 Object Library
everything worked perfectly. The spelling was OK in the code I just screwed
up in the post.

Is there anything else that should be added in the references as a genearl
rule?

Thanks for everything!

Regards,
FatMan
 
F

fredg

Fred:
Thanks for the help. Once I added the Microsoft DAO 3.6 Object Library
everything worked perfectly. The spelling was OK in the code I just screwed
up in the post.

Is there anything else that should be added in the references as a genearl
rule?

Thanks for everything!

Regards,
FatMan
Click to expand...

As a general rule? Nope.
As long as your code compiles you're fine.
Access 2000 installs the ADO library by default. You just needed to
add the DAO because that's what the code called for. You now have both
installed.
 
F

FatMan

Fred:
Thanks for all your help. You have helped me to add the "finishing" touches
to our Stock Take Database. This has been a project that I have been working
on and constantly trying to improve now for about four years. With these
finishing touches I think I can finally put it to bed and concentrate more on
some other projects. I am sure that will only mean more questions.

Thank you Fred for your help and thanks to everyone else that offeres advice
in these discussion groups, your help is greatly appreciated.

Thanks,
FatMan
 
Top