SQL syntax in VBA for Access
 

Hi,

I'm trying to return data from access into excel. I've tried using MS
Query, but I find it clunky and difficult to use. I'm trying to use vba to
connect to the db and return the data I want that way. All I have to is get
my SQL string to be properly formatted to work via the VBA, which is where
I'm hitting the hurdles. I know the syntax is not exactly the same, but I'm
not getting the proper format.


How would the following cross-tab query need to change?

TRANSFORM First([100_L1_Index].[100L1DlyIndx]) AS 100L1DlyIndx
SELECT [100_L1_Index].Date
FROM 100_L1_Index INNER JOIN G_L1 ON [100_L1_Index].Sect = G_L1.L1G
GROUP BY [100_L1_Index].Date
ORDER BY G_L1!L1G & " - " & G_L1!L1GName
PIVOT G_L1!L1G & " - " & G_L1!L1GName;

Thank you very much.
Kohai

SQL syntax in VBA for Access
 

I usually set my SQL statements like this:

Dim strSQL As String

strSQL = "TRANSFORM First([100_L1_Index].[100L1DlyIndx]) AS 100L1DlyIndx "
strSQL = strSQL & "SELECT [100_L1_Index].Date "
strSQL = strSQL & "FROM 100_L1_Index INNER JOIN G_L1 ON [100_L1_Index].Sect
= G_L1.L1G "
strSQL = strSQL & "GROUP BY [100_L1_Index].Date "
strSQL = strSQL & "ORDER BY G_L1!L1G & " & Chr(34) & " - " & Chr(34) & " &
G_L1!L1GName "
strSQL = strSQL & "PIVOT G_L1!L1G & " & Chr(34) & " - " & Chr(34) & " &
G_L1!L1GName;"

I then pass them to SQL Server or Access using ADO. I think you should be
able to use this to do what you are trying to do. I am thinking your issue
is with the quotes in the SQL string. The actual context should be the same.

Let me know if this does not help.
--
Happy Coding,

Scott


"kohai" wrote:

Hi,

I'm trying to return data from access into excel. I've tried using MS
Query, but I find it clunky and difficult to use. I'm trying to use vba to
connect to the db and return the data I want that way. All I have to is get
my SQL string to be properly formatted to work via the VBA, which is where
I'm hitting the hurdles. I know the syntax is not exactly the same, but I'm
not getting the proper format.


How would the following cross-tab query need to change?

TRANSFORM First([100_L1_Index].[100L1DlyIndx]) AS 100L1DlyIndx
SELECT [100_L1_Index].Date
FROM 100_L1_Index INNER JOIN G_L1 ON [100_L1_Index].Sect = G_L1.L1G
GROUP BY [100_L1_Index].Date
ORDER BY G_L1!L1G & " - " & G_L1!L1GName
PIVOT G_L1!L1G & " - " & G_L1!L1GName;

Thank you very much.
Kohai

SQL syntax in VBA for Access
 

Scott,

Thanks for the tip. Turns out that the quotes around my dashes are what
threw the thing off. I thought the brackets might have also been the
culprit, but they were ok.
Thanks again.

Kohai.

"Scott Vincent" wrote:

I usually set my SQL statements like this:

Dim strSQL As String

strSQL = "TRANSFORM First([100_L1_Index].[100L1DlyIndx]) AS 100L1DlyIndx "
strSQL = strSQL & "SELECT [100_L1_Index].Date "
strSQL = strSQL & "FROM 100_L1_Index INNER JOIN G_L1 ON [100_L1_Index].Sect
= G_L1.L1G "
strSQL = strSQL & "GROUP BY [100_L1_Index].Date "
strSQL = strSQL & "ORDER BY G_L1!L1G & " & Chr(34) & " - " & Chr(34) & " &
G_L1!L1GName "
strSQL = strSQL & "PIVOT G_L1!L1G & " & Chr(34) & " - " & Chr(34) & " &
G_L1!L1GName;"

I then pass them to SQL Server or Access using ADO. I think you should be
able to use this to do what you are trying to do. I am thinking your issue
is with the quotes in the SQL string. The actual context should be the same.

Let me know if this does not help.
--
Happy Coding,

Scott


"kohai" wrote:

Hi,

I'm trying to return data from access into excel. I've tried using MS
Query, but I find it clunky and difficult to use. I'm trying to use vba to
connect to the db and return the data I want that way. All I have to is get
my SQL string to be properly formatted to work via the VBA, which is where
I'm hitting the hurdles. I know the syntax is not exactly the same, but I'm
not getting the proper format.


How would the following cross-tab query need to change?

TRANSFORM First([100_L1_Index].[100L1DlyIndx]) AS 100L1DlyIndx
SELECT [100_L1_Index].Date
FROM 100_L1_Index INNER JOIN G_L1 ON [100_L1_Index].Sect = G_L1.L1G
GROUP BY [100_L1_Index].Date
ORDER BY G_L1!L1G & " - " & G_L1!L1GName
PIVOT G_L1!L1G & " - " & G_L1!L1GName;

Thank you very much.
Kohai