Is there a way to create a macro that would look in a certain cell in a
worksheet to pick which query in Access to use? All queries in Access would
have the same layout. I also want to use parameters that are in certain cell
in the same worksheet.

Tricky, but I like a challenge! MSQuery can take parameters, but not as
query or table names, just as selection criteria or for use in expressions.
The best thing I can think to do is to set up a basic querytable and then,
before refreshing, use VBA code to change the SQL query text
(querytable.commandtext) to customize it to your desires. You could do this
as part of the QueryTable.BeforeRefresh event procedure.

Using MSQuery, set up a simple query with any one of your Access queries -
just get a resulting querytable set up that matches one possible set of
parameters. Without knowing more about your database and spreadsheet I
can't develop this idea fully, but here is code that parses the SQL string
into its SELECT, FROM and WHERE components (assuming that is all we have!):

Public Sub ParseSQL()

Dim SELECTTxt As String, FROMTxt As String, WHERETxt As String
Dim TextPos As Integer

SQLTxt = Sheets("Sheet2").QueryTables(1).CommandText
TextPos = InStr(SQLTxt, vbCrLf & "FROM ")
SELECTTxt = Left(SQLTxt, TextPos - 1)
TextPos = InStrRev(SQLTxt, vbCrLf & "WHERE ")
WHERETxt = Right(SQLTxt, Len(SQLTxt) - TextPos + 1)
FROMTxt = Trim(Replace(SQLTxt, SELECTTxt, ""))
FROMTxt = Trim(Replace(FROMTxt, WHERETxt, ""))

MsgBox SELECTTxt
MsgBox FROMTxt
MsgBox WHERETxt

End Sub

Now (knowing the form of your query and the parameters you are getting from
your spreadsheet) you could use your VBA code to change these as necessary.
Substitute the Access query name in the FROMTxt with the cell value you want
to use to select the query. Substitute any other parameters in the WHERETxt
with your new parameters from the spreadsheet. Put this back together:

Sheets("Sheet2").QueryTables(1).CommandText = SELECTTxt & vbCrLF & FROMTxt &
vbCrLf & WHERETxt

Then refresh the query.

I have not had a chance to fully test this, but it seems to me it should
work. Hope I gave you at least an idea to work on.

"Steve" wrote:

Is there a way to create a macro that would look in a certain cell in a
worksheet to pick which query in Access to use? All queries in Access would
have the same layout. I also want to use parameters that are in certain cell
in the same worksheet.

Thanks. I will try this out in a few days when I get the chance and let you
know how it went.

"K Dales" wrote:

Tricky, but I like a challenge! MSQuery can take parameters, but not as
query or table names, just as selection criteria or for use in expressions.
The best thing I can think to do is to set up a basic querytable and then,
before refreshing, use VBA code to change the SQL query text
(querytable.commandtext) to customize it to your desires. You could do this
as part of the QueryTable.BeforeRefresh event procedure.

Using MSQuery, set up a simple query with any one of your Access queries -
just get a resulting querytable set up that matches one possible set of
parameters. Without knowing more about your database and spreadsheet I
can't develop this idea fully, but here is code that parses the SQL string
into its SELECT, FROM and WHERE components (assuming that is all we have!):

Public Sub ParseSQL()

Dim SELECTTxt As String, FROMTxt As String, WHERETxt As String
Dim TextPos As Integer

SQLTxt = Sheets("Sheet2").QueryTables(1).CommandText
TextPos = InStr(SQLTxt, vbCrLf & "FROM ")
SELECTTxt = Left(SQLTxt, TextPos - 1)
TextPos = InStrRev(SQLTxt, vbCrLf & "WHERE ")
WHERETxt = Right(SQLTxt, Len(SQLTxt) - TextPos + 1)
FROMTxt = Trim(Replace(SQLTxt, SELECTTxt, ""))
FROMTxt = Trim(Replace(FROMTxt, WHERETxt, ""))

MsgBox SELECTTxt
MsgBox FROMTxt
MsgBox WHERETxt

End Sub

Now (knowing the form of your query and the parameters you are getting from
your spreadsheet) you could use your VBA code to change these as necessary.
Substitute the Access query name in the FROMTxt with the cell value you want
to use to select the query. Substitute any other parameters in the WHERETxt
with your new parameters from the spreadsheet. Put this back together:

Sheets("Sheet2").QueryTables(1).CommandText = SELECTTxt & vbCrLF & FROMTxt &
vbCrLf & WHERETxt

Then refresh the query.

I have not had a chance to fully test this, but it seems to me it should
work. Hope I gave you at least an idea to work on.

"Steve" wrote:

Is there a way to create a macro that would look in a certain cell in a
worksheet to pick which query in Access to use? All queries in Access would
have the same layout. I also want to use parameters that are in certain cell
in the same worksheet.

Here is something I came up with and maybe you can help here.

Sub SelectTable()
'
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\swells\Desktop\GL Detail.mdb;DefaultDir=C:\Documents and
Settings\swells\D" _
), Array("esktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("A4"))
.CommandText = Array( _
"SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT" & Chr(13) & "" &
Chr(10) & "FROM `C:\Documents and Settings\swells\Desktop\GL Detail`.`GLPJC -
BIR`" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I would like to be able to change the table called "GLPJC - BIR" in the FROM
statement to whatever field I want. In the beginning of the code, I thought
of getting the table name from a specific cell using the following:

MyTable = Range("A1").Text

Any suggestions on how I can do this in the FROM statement?



"Steve" wrote:

Is there a way to create a macro that would look in a certain cell in a
worksheet to pick which query in Access to use? All queries in Access would
have the same layout. I also want to use parameters that are in certain cell
in the same worksheet.

dim SQL as string, MyTable as string


MyTable = Range("A1").Text
SQL= "SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT " & _
"FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`" & MyTable & "`"



..CommandText = Array(SQL)




You don't need the chr(10)+chr(13)

Tim.


"Steve" wrote in message
...
Here is something I came up with and maybe you can help here.

Sub SelectTable()
'
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\swells\Desktop\GL Detail.mdb;DefaultDir=C:\Documents and
Settings\swells\D" _
), Array("esktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("A4"))
.CommandText = Array( _
"SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT" & Chr(13) &
"" &
Chr(10) & "FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`GLPJC -
BIR`" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I would like to be able to change the table called "GLPJC - BIR" in
the FROM
statement to whatever field I want. In the beginning of the code, I
thought
of getting the table name from a specific cell using the following:

MyTable = Range("A1").Text

Any suggestions on how I can do this in the FROM statement?



"Steve" wrote:

Is there a way to create a macro that would look in a certain cell
in a
worksheet to pick which query in Access to use? All queries in
Access would
have the same layout. I also want to use parameters that are in
certain cell
in the same worksheet.

Thanks Tim, I will try out and let you know how it works.

"Tim Williams" wrote:

dim SQL as string, MyTable as string


MyTable = Range("A1").Text
SQL= "SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT " & _
"FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`" & MyTable & "`"



..CommandText = Array(SQL)




You don't need the chr(10)+chr(13)

Tim.


"Steve" wrote in message
...
Here is something I came up with and maybe you can help here.

Sub SelectTable()
'
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\swells\Desktop\GL Detail.mdb;DefaultDir=C:\Documents and
Settings\swells\D" _
), Array("esktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("A4"))
.CommandText = Array( _
"SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT" & Chr(13) &
"" &
Chr(10) & "FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`GLPJC -
BIR`" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I would like to be able to change the table called "GLPJC - BIR" in
the FROM
statement to whatever field I want. In the beginning of the code, I
thought
of getting the table name from a specific cell using the following:

MyTable = Range("A1").Text

Any suggestions on how I can do this in the FROM statement?



"Steve" wrote:

Is there a way to create a macro that would look in a certain cell
in a
worksheet to pick which query in Access to use? All queries in
Access would
have the same layout. I also want to use parameters that are in
certain cell
in the same worksheet.

Tim, I have changed my code based on your example and am getting an error
message with the description SQL Syntax Error. When I go to debug, it
highlights the 2nd to last code line ".Refresh BackgroundQuery:= False". Not
sure where to go from here. Any help would be appreciated. Thanks.

MyTable = Range("A1").Text
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\swells\Desktop\GL Detail.mdb;DefaultDir=C:\Documents and
Settings\swells\D" _
), Array("esktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("A4"))
Sql = "SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT" & "FROM
`C:\Documents and Settings\swells\Desktop\GL Detail`.`" & MyTable & "`"
.CommandText = Array(Sql)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With



"Tim Williams" wrote:

dim SQL as string, MyTable as string


MyTable = Range("A1").Text
SQL= "SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT " & _
"FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`" & MyTable & "`"



..CommandText = Array(SQL)




You don't need the chr(10)+chr(13)

Tim.


"Steve" wrote in message
...
Here is something I came up with and maybe you can help here.

Sub SelectTable()
'
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\swells\Desktop\GL Detail.mdb;DefaultDir=C:\Documents and
Settings\swells\D" _
), Array("esktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("A4"))
.CommandText = Array( _
"SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT" & Chr(13) &
"" &
Chr(10) & "FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`GLPJC -
BIR`" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I would like to be able to change the table called "GLPJC - BIR" in
the FROM
statement to whatever field I want. In the beginning of the code, I
thought
of getting the table name from a specific cell using the following:

MyTable = Range("A1").Text

Any suggestions on how I can do this in the FROM statement?



"Steve" wrote:

Is there a way to create a macro that would look in a certain cell
in a
worksheet to pick which query in Access to use? All queries in
Access would
have the same layout. I also want to use parameters that are in
certain cell
in the same worksheet.

You haven't left a space between COMMENT and FROM in your SQL string:
that's why you're getting a syntax error.

Tim.


"Steve" wrote in message
...
Tim, I have changed my code based on your example and am getting an
error
message with the description SQL Syntax Error. When I go to debug,
it
highlights the 2nd to last code line ".Refresh BackgroundQuery:=
False". Not
sure where to go from here. Any help would be appreciated. Thanks.

MyTable = Range("A1").Text
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\swells\Desktop\GL Detail.mdb;DefaultDir=C:\Documents and
Settings\swells\D" _
), Array("esktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("A4"))
Sql = "SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT" & "FROM
`C:\Documents and Settings\swells\Desktop\GL Detail`.`" & MyTable &
"`"
.CommandText = Array(Sql)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With



"Tim Williams" wrote:

dim SQL as string, MyTable as string


MyTable = Range("A1").Text
SQL= "SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT " & _
"FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`" & MyTable & "`"



..CommandText = Array(SQL)




You don't need the chr(10)+chr(13)

Tim.


"Steve" wrote in message
...
Here is something I came up with and maybe you can help here.

Sub SelectTable()
'
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\swells\Desktop\GL Detail.mdb;DefaultDir=C:\Documents and
Settings\swells\D" _
), Array("esktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")) _
, Destination:=Range("A4"))
.CommandText = Array( _
"SELECT POSTINGSEQ, AUDTUSER, AUDTORG, COMMENT" & Chr(13)
&
"" &
Chr(10) & "FROM `C:\Documents and Settings\swells\Desktop\GL
Detail`.`GLPJC -
BIR`" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I would like to be able to change the table called "GLPJC - BIR"
in
the FROM
statement to whatever field I want. In the beginning of the
code, I
thought
of getting the table name from a specific cell using the
following:

MyTable = Range("A1").Text

Any suggestions on how I can do this in the FROM statement?



"Steve" wrote:

Is there a way to create a macro that would look in a certain
cell
in a
worksheet to pick which query in Access to use? All queries in
Access would
have the same layout. I also want to use parameters that are in
certain cell
in the same worksheet.