copy table

  • Thread starter Thread starter Ansar Malik
  • Start date Start date
A

Ansar Malik

Guest
Hi,

Can anyone let me know how to write Vb code to do something like this

Delete * from mytable;

PARAMETERS [tab] Text ( 255 );
INSERT INTO mytable
SELECT [tab].*
FROM [tab];

basically delete then copy aother table into it and be prompted for the table name.

thanks
 
If you using VB6 and not VBA for Access then you will need the Win32 DBEngine to do what you want. After you establish a connection with the DBEngine you need to use the Execute method to execute the SQL statement you desire.

It helps to make sure you have the latest MDAC from Microsoft I believe 2.8 is the latest version.

Search the following site for more help http://www.visualbasicforum.com/
 
This will allow you to copy a table and rename it within the open db...

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "TableToCopy", "NewTableName", False

This will allow you delete a table...

DoCmd.DeleteObject acTable, "TableToDelete"

HTH
 
Thats has started me of nicely with visual basic for access.
though the code you provide for tablename is hard coded

thanks
 
To: ghudson

>>This will allow you delete a table...

DoCmd.DeleteObject acTable, "TableToDelete"

It does not delete the table for me....

The error I get is :
"Runtime Error 3211: the database engine could not lock table "TableToDelete" because it is already in use by another person or process."

Only I have the db under development and am the only person using it

Here is my actual code:

Private Sub Form_Close()
DoCmd.DeleteObject acTable, "tblTEMP"
End Sub

I'm also running A2K

What did you do ?

Marty
 
Marty,

I'll bet that the form that the code is attached to has the table
as its record source. You really can't delete it while you're
using it.

This caught my interest. I don't programmatically create tables
so I thought I'd give it a try.

The attached is just for fun (it doesn't have any real use for me),
but I hope it helps you.

Wayne
 

Attachments

oops,

I was playing around with embedded-space names. It made
'em OK, but I didn't test it one more time to fix the Drop
Table sql.

Oh well,

Wayne
 

Attachments

Thanks to all ...

but in particular to you WayneRyan

you solved my problem -->
It was:
1) the correct use of SQL ;
- use "drop table" instead of "docmd.DeleteObject acTable"

2) I suspected the "can't delete table while you're using it" - so I moved the code all over the place, but finally settled on the first place where I wrote the code - near the beginning of the preceeding screen, so that just before I create the new table, I delete the old one. Of course, this eliminates the "can't delete" problem, because the table.

Marty H.
 

Users who are viewing this thread

Back
Top Bottom