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
  4.  
  5. strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Database.mdb;" & _
  6. "Jet OLEDB:Engine Type=4"
  7.  
  8. Set objConn = New ADODB.Connection
  9. objConn.Open strConnectionString
  10.  
  11. Set objCommand = New ADODB.Command
  12.  
  13. objCommand.ActiveConnection = objConn
  14.  
  15. ' Assign to ADO Command object
  16. objCommand.CommandText = "QRY_ONE"
  17. objCommand.CommandType = adCmdStoredProc
  18.  
  19. objCommand.Execute
  20.  
  21. objConn.Close
  22.  
  23. Set objCommand = Nothing
  24. Set objConn = Nothing
  25.  
Thanks
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.