SQL syntax in VBA for Access

[kohai]

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

[Scott Vincent]

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

[kohai]

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