Avoid prompts with QueryTables

[Guest]

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?

 

[Guest]

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

 

[Tom Ogilvy]

Specify the database in the code.

 

[Guest]

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?

 

[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.

 

[Guest]

Tom --

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