Avoid prompts with QueryTables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wolverine - 19 Mar 2006 19:09 GMT

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?

Reply to this Message

sebastienm - 19 Mar 2006 19:22 GMT

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

Signature

Regards,
Sébastien
<http://www.ondemandanalysis.com>

> 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?

Reply to this Message

Tom Ogilvy - 19 Mar 2006 20:16 GMT

Specify the database in the code.

Signature

Regards,
Tom Ogilvy

Reply to this Message

wolverine - 20 Mar 2006 15:48 GMT

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?

> Specify the database in the code.
>
[quoted text clipped - 3 lines]
> > proceed without any user interaction once it's started. How do I prevent
> > this prompt from popping up?

Reply to this Message

Tom Ogilvy - 21 Mar 2006 02:51 GMT

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.

Signature

Regards,
Tom Ogilvy

> Sebastien / Tom,
>
[quoted text clipped - 20 lines]
> > > proceed without any user interaction once it's started. How do I prevent
> > > this prompt from popping up?

Reply to this Message

wolverine - 21 Mar 2006 16:18 GMT

Tom --

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

> Sub Macro2()
> Dim sConn As String, sSql As String
[quoted text clipped - 58 lines]
> prevent
> > > > this prompt from popping up?

Reply to this Message