469,090 Members | 1,245 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

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

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
1 4786
Killer42
8,435 Expert 8TB
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.

Similar topics

1 post views Thread by Vincent Jones | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.