SQL Help!

[ExcelKid_10]

Hi all-

I need some expert advice on what I am doing wrong on my SQL/VBA code below....

Sub FundName()
Dim db As Database
Dim strSQL As String
Dim rs As Recordset
Dim FName As Variant

strSQL = "INSERT INTO tblTopExposures ( [Date], Port, FundOrAcct, Cusip, Description, [%MV] )" & _
"SELECT TOP 10 tblHoldings.Date, tblHoldings.Port, tblHoldings.FundOrAcct, tblHoldings.Cusip, tblHoldings.Description, [qry%MV].[%MV]" & _
"FROM [qry%MV] INNER JOIN tblHoldings ON ([qry%MV].Cusip = tblHoldings.Cusip) AND ([qry%MV].FundOrAcct = tblHoldings.FundOrAcct) AND ([qry%MV].Port = tblHoldings.Port) AND ([qry%MV].Date = tblHoldings.Date)" & _
"Where Port = FName;"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do Until rs.EOF
FName = rs
DoCmd.RunSQL strSQL
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub

It keeps bombing out on me when the code hits this:

Set rs = CurrentDb.OpenRecordset(strSQL)

The end product of my SQL is simply to append the data using a loop that passes a Port name through as the criteria.

Any help is much appreciated!

Thanks!

EK

 

[Andrew Fergus]

Hi

Have you tried running the query without using VBA? If you can get that to work using a query, then copy the SQL into your VBA routine. I haven't looked at your code in detail but check you have spaces between various words where you are using the ampersand (&) symbol to join strings together - it looks like you are missing a few.

Andrew

 

[ExcelKid_10]

Thanks Andrew!

I've made the changes you recommended but still cannot get my SQL to work. Here's the simple breakdown of my data....

[Date]-Date
Port-Text
FundOrAcct-Text
Cusip-Text
Description-Text
[%MV]-Number

In my tblFundsOrAccts I have a unique list of Port names. What I would like to do is go through each one and use it as the criteria for the INSERT query.

Any more thoughts on how I could make this work?

Thanks!!

EK

 

[ExcelKid_10]

OK. I've been playing with it and I am almost there....what I have is this....

Sub FundName()
Dim db As Database
Dim strSQL As String
Dim rs As Recordset
Dim FName As Variant

strSQL = "INSERT INTO tblTopExposures ( [Date], Port, FundOrAcct, Cusip, Description, [%MV] ) SELECT TOP 10 tblHoldings.Date, tblHoldings.Port, tblHoldings.FundOrAcct, tblHoldings.Cusip, tblHoldings.Description, [qry%MV].[%MV]FROM [qry%MV] INNER JOIN tblHoldings ON ([qry%MV].Cusip = tblHoldings.Cusip) AND ([qry%MV].FundOrAcct = tblHoldings.FundOrAcct) AND ([qry%MV].Port = tblHoldings.Port) AND ([qry%MV].Date = tblHoldings.Date)WHERE tblHoldings.Port='FName';"

Set rs = CurrentDb.OpenRecordset("SELECT Port FROM tblFundsOrAccts")


With rs
Do Until rs.EOF
FName = rs!Port
DoCmd.RunSQL strSQL
rs.MoveNext
Loop
End With

rs.Close
Set rs = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub


The query works. And it looks as if the FName is being passed through as a variable in the query. However, it is not appending any data (and there is data there). Any thoughts??

Thanks!

EK

 

[ExcelIsEvil]

How about this


strSQL = "INSERT "
strSQL = strSQL & " INTO tblTopExposures ( [Date], Port, FundOrAcct, Cusip, Description, [%MV] ) "

strSQL = strSQL & " SELECT TOP 10 tblHoldings.Date, tblHoldings.Port, tblHoldings.FundOrAcct, tblHoldings.Cusip, tblHoldings.Description, [qry%MV].[%MV] "

strSQL = strSQL & " FROM [qry%MV] "

strSQL = strSQL & " INNER JOIN tblHoldings ON ([qry%MV].Cusip = tblHoldings.Cusip) "

strSQL = strSQL & " AND ([qry%MV].FundOrAcct = tblHoldings.FundOrAcct) "

strSQL = strSQL & " AND ([qry%MV].Port = tblHoldings.Port) "

strSQL = strSQL & " AND ([qry%MV].Date = tblHoldings.Date)WHERE tblHoldings.Port='FName';"

<<

But are you sure that you can do an INSERT via that conn connection?