VBA SQL Help

[DonkeyKong]

I have this little ditty that I would reallly like to work. I'm actually copying this from an existing database and trying to get it to work for mine. So far I have had absolutely no luck. The other db used a query, I am using VB. I don't know what I am doing wrong. All the objects are valid. Do I need to do something with the relationships between tables? Please help!

strSQL = "SELECT [Principal.Cusip], [Interest.[Payable Date]], [Interest.Interest]," & _
"[Principal.Principal], [Interest.Interest]+[Principal.Principal] AS Total, [DTC Amounts Paid]" & _
"FROM Interest INNER JOIN Principal ON [Interest.Cusip] = [Principal.Cusip]" & _
"WHERE ((([Principal.Cusip]) Is Not Null))"
 CurrentDb.Execute strSQL, dbFailOnError

 

[spikepl]

You have failed to mention what is wrong - when asking for help always say what you expected and what you got.


Actually, in this particular instance, I am sure that Access tells you pretty accurately what the problem is - read the error message again. If something in the message is not clear, ask again.

Also, for writing SQL in VBA, make it a habit to do it like this: http://www.baldyweb.com/ImmediateWindow.htm

 

[DonkeyKong]

Thanks spike. The problem I'm having is that it doesn't work. It reads the sql, then it goes to the currentdb.execute and errors out. My fail message consist of a msgbox that says "Error". I will look over the link you sent me and see if I can figure it out through that. I was thinking that there is probably a problem with my syntax somewhere so I guess I'm just asking for a little proof reading and pointing out my errors.

 

[spikepl]

You have enabled error handling while debugging, which is not a a good thing to do. In your subroutine, comment out the On Error Goto statement

 

[pbaldy]

2 problems I notice, 1 which the link should help you see (hint, spaces), the other not so obvious: you can't "execute" a simple SELECT query, only action queries.

 

[DonkeyKong]

Good to know. Learning new stuff everyday. The basic layout of most of the VB that I code comes from previously written programs which I don't have a full understanding of. I have removed that code and have included the debug.print strSQL.

The error that comes up is "Cannot execute a selected query".

I also got the query cleaned up a bit using the print function. I got a little bracket happy when I couldn't get anything to work and figured that I could just bracket all of the commands to clarify things.

NOW I can get it to run the query from the query screen in access. However, it asks me to enter a value for DTC Amounts Paid table. I just press 'enter' and it gives me the correct table. Do you know what I must do to satisfy this requirement. Thanks again for the help.

 

[DonkeyKong]

Ok. I have it updated a bit. The error from the VB compiler is that "DTC Amounts Paid" already exists. It apperantly doesn't like that I already have this table already in existence. I just want to update the same table everytime, not create a new one every time. Do you know what I need? I will see if I can't figure it out, no luck yet though.

PS. Pat Hartman, the query runs fine now. Just the VB doesn't work.

 

[DonkeyKong]

Here is the updated code:

strSQL = "SELECT Principal.Cusip, Interest.[Payable Date], Interest.Interest, " & _
"Principal.Principal, Interest.Interest+Principal.Principal AS Total INTO [DTC Amounts Paid] " & _
"FROM Interest INNER JOIN Principal ON Interest.Cusip = Principal.Cusip " & _
"WHERE (((Principal.Cusip) Is Not Null))"

Here is the full program minus the seperate functions:

Function InterestImport()
 
Dim dbsCompare As DAO.Database
Dim rst As DAO.Recordset
 
   Set dbsCompare = CurrentDb
 
ImportInterestTxt dbsCompare
ImportPrincipalTxt dbsCompare
 
strSQL = "SELECT Principal.Cusip, Interest.[Payable Date], Interest.Interest, " & _
"Principal.Principal, Interest.Interest+Principal.Principal AS Total INTO [DTC Amounts Paid] " & _
"FROM Interest INNER JOIN Principal ON Interest.Cusip = Principal.Cusip " & _
"WHERE (((Principal.Cusip) Is Not Null))"
 
Debug.Print strSQL
 
CurrentDb.Execute strSQL, dbFailOnError
 
End Function

 

[DonkeyKong]

Pat Hartman - I tried using

CurrentDb.OpenRecordset strSQL, dbFailOnError

instead but got the error message Invalid Argument.

Also the reason that I'm doing it this way is that a) I'm better this... which says something about my skill b) I am trying to do some complex activities with one click that is not the easiest thing to do with the macro creator. I didn't want to make the specs for import when I could do it easily with VB AND I could edit them. I am a novice admittedly but eventually I will get better and will use queries for stand alone queries. I want this one to occur along with everything else though. Is that a good reason or do you still think that I should go about it another way?

 

[DonkeyKong]

Bump. Still need the magic coding. Anyone?

 

[pbaldy]

I don't think dbFailOnError is a valid argument when opening a recordset. You can look in help to see.

 

[DonkeyKong]

pBaldy - you speak in riddles. I'm trying to understand, but why would execute not be the correct use? I am performing an inner join. Wouldn't that be an executable command? I tried using the help function and got:

CurrentDb.OpenRecordset StrSQL, dbOpenSnapshot

It didn't work. I got an 'Invalid Operation' error.

Can't you just tell me what I need!?!?!

 

[DonkeyKong]

Ok, I tried this. Still no luck.

Set rst = dbs.OpenRecordset("SELECT DISTINCTROW " _
    & "Principal.Cusip, Interest.[Payable Date], Interest.Interest " _
    & "Principal.Principal, Interest.Interest+Principal.Principal AS Total Into [DTC Amounts Paid] " _
    & "From Interst INNER JOIN Principal On Interest.Cusip = Principal.Cusip " _
    & "GROUP BY (Principal.Cusip);")
rst.MoveLast

with or without the last GROUP BY statement it doesn't work.

 

[DonkeyKong]

I'm expecting my table to be populated the same as it is when I run that same code in the SQL query format.

Why is it any different from another query? I have delete query's executing? What's the difference between a SELECT and a DELETE?

Point is I have a button that I push and then it will run the imports and it will get the combined list from both imports that I want using this query. And I can have it all in one module under one button. I will do seperate query's later but for right now I'm just trying to get this button done.