Avoid prompts with QueryTables

W

wolverine

I am a VBA beginner and have managed to pull together an MS Access query
using the QueryTables feature. However, each time I run my code, I get a
dialog box asking me to select the database. I would like the code to
proceed without any user interaction once it's started. How do I prevent
this prompt from popping up?
 
S

sebastienm

Have you tried to set the DisplayAlerts property before and after the code
generating the prompt?
Application.DisplayAlerts = False
'... your code here
Application.DisplayAlerts = True
 
W

wolverine

Sebastien / Tom,

Thanks for your suggestions. Unfortunately, I'm still having the problem.
When I use the DisplayAlerts property, the database is not queried at all. I
already reference the database in my code...or at least I think I do. Here
is some of my QueryTable code and the connection string:

connstring = "ODBC;DSN=MS Access Database;Database=C:\Documents and
Settings\u223535\Desktop\test database\test database"

With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Worksheets.Add.Range("A1"), Sql:=sqlstring)
.Refresh BackgroundQuery:=False
End With

Any thoughts?
 
T

Tom Ogilvy

Sub Macro2()
Dim sConn As String, sSql As String
sConn = "ODBC;DSN=MS Access 97 Database" & _
";DBQ=c:\Data\Northwind.mdb;DefaultDir=" & _
"c:\Data;DriverId=281;FIL=MS Access"
sSql = "SELECT * FROM `c:\Data\" & _
"Northwind`.Employees Employees"
With ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("C9"))
.Sql = sSql
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub


worked for me with no prompt. (xl 97)

Are your strings shorter than 255 characters. I see your connection string
is 106. (I also don't see .mdb on the end. Try adding that). However,
your sql string may be long and you don't show that. If it is I think you
have to break it up in an array. Try doing the query manually with the
macro recorder turned on and see what you record.
 
W

wolverine

Tom --

I'm not sure which of your suggestions did it, but it works! Thanks so much
for your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top