By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,978 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,978 IT Pros & Developers. It's quick & easy.

Allowing Make Table Query overwriting existing table in mdb via adodb connection

P: 1
Hi I am using the following code in a button click event to run a query stored in a .mdb file from an external geographic program ArcGIS via a jet adodb connection

I have the need to run an existing make table query, my problem is it will over write an existing table. i.e when run in access a warning that you are about to overwrite an existing table will be shown and you will be prompted to accept or reject. When run via the external program using the method below a run time error occurs saying the table to be overwriten already exists. I am unsure how to access the Accept reject overwrite function via the external program if it is possible at all. Any ideas on how to hand this error would be greatly appreciated

Expand|Select|Wrap|Line Numbers
  1. Dim strConnectionString As String
  2. Dim objConn As ADODB.Connection
  3. Dim objCommand As ADODB.Command
  5. strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Database.mdb;" & _
  6. "Jet OLEDB:Engine Type=4"
  8. Set objConn = New ADODB.Connection
  9. objConn.Open strConnectionString
  11. Set objCommand = New ADODB.Command
  13. objCommand.ActiveConnection = objConn
  15. ' Assign to ADO Command object
  16. objCommand.CommandText = "QRY_ONE"
  17. objCommand.CommandType = adCmdStoredProc
  19. objCommand.Execute
  21. objConn.Close
  23. Set objCommand = Nothing
  24. Set objConn = Nothing
Sep 28 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 5K+
P: 8,434
Why not just trap the "already exists" error, prompt the user, and if they say to go ahead, delete the existing table first? That's probably all that Access does.
Sep 28 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.