Web Development

Ask something
Discussion Locked

Make-Table in Access

0 helpful
5 replies

by bristar

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have the following SQL:
"SELECT * INTO tblQRY FROM tblActionList"
How do I do this make table query to have it automatically choose yes to delete the current table and yes to paste those records?

5 replies

by richtee

If you are using Access2K: Insert this before you run your code

DoCmd.Hourglass True
DoCmd.SetWarnings False

and then this after the RunSql statement

DoCmd.Hourglass False
DoCmd.SetWarnings True

This will do it. Rich

by miss kitty

Write a delete query in Access and then before you do the insert, run the query from VB and delete all the info in the table. The table will not be deleted, but the rows will be emptied. Here's an example of how to do this in vb.

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRS As New ADODB.Recordset
Dim objParam As ADODB.Parameter
Dim count As Integer

'set up the database pointer
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=E:\ACCESS\CONTRACT HAULERS\HAULER.mdb;User ID=ADMIN"
objRS.CursorType = adOpenDynamic
objRS.LockType = adLockOptimistic

'setting the Active Connection for the command object
objCmd.ActiveConnection = objConn

'Providing the query name as commandtext
objCmd.CommandText = "[DELETE TTICKET QUERY]"
objCmd.CommandType = adCmdStoredProc

'Set the recordset to the command object result
Set objRS= objCmd.Execute

Good Luck
Cathy
covcat@riverstonegrp.com

This discussion is closed to new replies.