HELP

[emma_b79]

I need help with microsoft access specifically programming vb in microsoft access

Comments

[emma_b79]

I have got append queries that do the job but I just dont want loads of them cluttering up the database.

Basically what i want is a routine that will change the parameters on the append query, append it to a table, change the parameters, append it to a table etc.

I've got VB code saying:

'TOTALTOTALTOTAL

strSql = "INSERT INTO [Past Due In Table] ( PL, [P & L], CustName, [Value] ) " & vbCrLf & _
"SELECT TOP 5 ""TOTALTOTALTOTAL"" AS PL, " & vbCrLf & _
"[Past Due In Detail].[P&L], " & vbCrLf & _
" [Past Due In Detail].CustName, " & vbCrLf & _
"[Past Due In Detail].Value " & vbCrLf & _
"FROM [Past Due In Detail] " & vbCrLf & _
"GROUP BY ""TOTALTOTALTOTAL"", [Past Due In Detail].[P&L], [Past Due In Detail].CustName, [Past Due In Detail].Value " & vbCrLf & _
"ORDER BY [Past Due In Detail].Value DESC;"

for an sql append query of

INSERT INTO [Past Due In Table] ( PL, [P & L], CustName, [Value] )
SELECT TOP 5 "TOTALEMEATOTAL" AS PL, [Past Due In Detail].[P&L], [Past Due In Detail].[CustName], [Past Due In Detail].[Value]
FROM [Past Due In Detail]
WHERE ((([Past Due In Detail].[P&L])="PII") And (([Past Due In Detail].[Entity])="EMEA"))
GROUP BY "TOTALEMEATOTAL", [Past Due In Detail].[P&L], [Past Due In Detail].[CustName], [Past Due In Detail].[Value]
ORDER BY [Past Due In Detail].[Value] DESC;

If you know of a way to get the parameters to change automatically and run the query again with the changed parameters please let me know.

I've tried using a macro and the Sendkeys option but my access version doesnt like it

[syllopsium]

Yup - you definitely want a stored procedure. You'll be able to pass your parameters and reduce the amount of code. Your worst case scenario should be a main routine taking parameters and another script/stored procedure calling it :

sp_appendmystuff(bread,cheese,milk)
sp_appendmystuff(cat,dog,pig)
sp_appendmystuff(wood,brass,iron)

[emma_b79]

so how do i get the stored procedure?

(this is the first time I've actually used anything harder than queries and simple macros in access so I'm struggling)

[emma_b79]

actually ignore this it was a silly question. Will come up with something better.

[emma_b79]

Ok i'm not sure what you mean can you explain?

[syllopsium]

Doing it in access is not something I'm experienced in.

However, although this is aimed at an ASP scenario it may well be helpful :

http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1

More detail here, although I think this one is out of date :

http://hubpages.com/hub/Stored_Procedure_in_MS_Access

[emma_b79]

thanks hon that helps :)