Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid prompts with QueryTables
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid prompts with QueryTables
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 -- Regards, Sébastien <http://www.ondemandanalysis.com "wolverine" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid prompts with QueryTables
Specify the database in the code.
-- Regards, Tom Ogilvy "wolverine" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid prompts with QueryTables
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" wrote: Specify the database in the code. -- Regards, Tom Ogilvy "wolverine" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid prompts with QueryTables
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. -- Regards, Tom Ogilvy "wolverine" wrote in message ... 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" wrote: Specify the database in the code. -- Regards, Tom Ogilvy "wolverine" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoid prompts with QueryTables
Tom --
I'm not sure which of your suggestions did it, but it works! Thanks so much for your help! "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "wolverine" wrote in message ... 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" wrote: Specify the database in the code. -- Regards, Tom Ogilvy "wolverine" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you avoid window that prompts to enable auto query refresh | Excel Discussion (Misc queries) | |||
QueryTables Add | Excel Programming | |||
QueryTables Error | Excel Programming | |||
With ActiveSheet.QueryTables.Add | Excel Programming |