bypass prompts in Access query (not SQL) run from VB 6.0

[Jenke]

Hey all,
I need to run a couple of Access queries when i close my application. They are make-table queries that create tables in a separate database. they work fine the first time, but do not successfully complete when access would normally prompt to overwrite the current table.
i need to be able to run the queries and automatically accept any prompts given.

anyone have a way around this?

thanks

j

 

[bjd4jc]

Are you executing these from VB6 or an Access VBA program? What prompts are you seeing? What does your code look like?

If you're using the object model then you'll want to use

Docmd.SetWarnings True

I think it is called from the Application Object

 

[Jenke]

hey im executing from the VB application, not within an access vba app..

my main problem is that im using a custom database component written a few years ago, then was passed on to me to update.. i dont have the source to see what its doing so im hoping to find a way around it.

(so the code probably wouldnt help since the function isnt native to vb)

i dont receive any prompts when i run it, nor do i want to. when the table that the access query exports to doesnt exist, it works fine through access and the vb app since there are no prompts. then 2nd time though in access it will ask if i want to append the data or overwrite the table or something of that nature.. if i say yes in access it works fine.. however the app just crashes..


thanks,

jon

 

[jebenson]

If the table name does not change, this code will allow you to delete the table from the database before running the import, thus avoiding those annoying prompts:

Dim Conn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
    
Set Conn = New ADODB.Connection
    
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\db1.mdb;Persist Security Info=False"
Conn.Open
    
Set cat = New ADOX.Catalog
    
cat.ActiveConnection = Conn
    
    
For Each tbl In cat.Tables
    If tbl.Name = "Table1" Then 'use your table's name here
        cat.Tables.Delete tbl.Name
    End If
Next

For this to work you need to reference Microsoft ActiveX Data Objects 2.x Library (for the Connection) and Microsoft ADO Ext. 2.x for DDL and Security (for the Catalog and Table objects). Also, you will of course need to provide your own ConnectionString to access the DB on your system.

Hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson