copy table

[Ansar Malik]

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

 

[Calvin]

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/

 

[ghudson]

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

 

[Ansar Malik]

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

thanks

 

[Martyh]

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

 

[WayneRyan]

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

 

[WayneRyan]

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

 

[Martyh]

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.