Run SQL in VB upon opening DB

[Hayley Baxter]

I want to run an SQL statement upon opening my database. I was planning on using an AutoExec macro which would do this for me but my sql statement is longer than the max256 char allowed in a macro so I have to do it in VBA. I am going to cut and paste the Sql from my query to whereever it needs to go but the problem is where to put this in VB? And will it automatically run when my db opens now that I cannot use the AutoExec macro?

Many thanks
Hayley

 

[Rich]

Use the Autoexec to run the code such as
Function Update()
On Error GoTo Update_Err
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Deductions ( TrDate, Debits, Heading, HowPaid, [Desc], Payee )SELECT DateDue.DateDue, DateDue.PaymentAmount, DateDue.Heading, DateDue.PymtType, DateDue.Description AS Det, DateDue.Payee FROM DateDue WHERE (((DateDue.DateDue)<=Date()) AND ((DateDue.PaymentAmount)<>0) AND ((DateDue.Appended)=No));"
DoCmd.RunSQL "INSERT INTO Account ( AccDate, Deductions, Heading2, [Description] )SELECT DateDue.DateDue, DateDue.PaymentAmount, 'BusDrawings' AS Head, [PymtType] & ' ' & 'to' & ' ' & [Payee] & ' ' & 'for' & ' ' & [Description] & ' ' &

AS Det FROM DateDue WHERE (((DateDue.DateDue)<=Date()) AND ((DateDue.PaymentAmount)<>0) AND ((DateDue.Appended)=No));"
DoCmd.RunSQL "UPDATE DateDue SET DateDue.Appended = Yes WHERE (((DateDue.DateDue)<=Date()) AND ((DateDue.PaymentAmount)<>0));"
DoCmd.RunSQL "DELETE DateDue.*, DateDue.DateDue FROM DateDue WHERE (((DateDue.DateDue)<=DateAdd('m',-3,Date()) And Not (DateDue.DateDue)=#1/1/94#));"
DoCmd.RunSQL "DELETE DateDue.*, DateDue.DateDue FROM DateDue WHERE (((DateDue.DateDue) Is Null));"

Update_Exit:
Exit Function

Update_Err:
MsgBox Error$
Resume Update_Exit

End Function​

 

[Hayley Baxter]

I am not getting very far with this. I take it I create a module called Function? I have tried to do this I can see the module named function when I open it to put code in but when I come back out to the db window it is not showing why? Also I copied your code and changed the sql statement to match mine but there is obviously something wrong because it is being highlighted in red. Can anyone tell me step by step what I need to do to implement this. I can't imagine I am too far off achieving this now. It is only the SELECT and WHERE lines showing in red.

Here's what I have:

Function Update()
On Error GoTo Update_Err
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblArchive ( [Supplier ID], ContractTypeID, [Warranty Terms], [Ownership Terms], [Insurance Terms], [Termination Terms], [Liability Terms], [Professional Indemnity Terms], [Intelectual Property Rights Terms], [Disaster Recovery Terms], [Rates Terms], [Expenses Terms], [Nature of Agreement Terms], [Confidentiality Terms], Definitions, [Export Lead Terms], [Delivarables Terms], [Support Terms], [Service Level Terms], [Response Times], [Entire Agreement and Governing Law], ProjectTypeID, [Contract Start Date], [Contract End Date], [Link 1], [Link 2] )"
SELECT tblContractualAgreements.[Supplier ID], tblContractualAgreements.ContractTypeID, tblContractualAgreements.[Warranty Terms], tblContractualAgreements.[Ownership Terms], tblContractualAgreements.[Insurance Terms], tblContractualAgreements.[Termination Terms], tblContractualAgreements.[Liability Terms], tblContractualAgreements.[Professional Indemnity Terms], tblContractualAgreements.[Intelectual Property Rights Terms], tblContractualAgreements.[Disaster Recovery Terms], tblContractualAgreements.[Rates Terms], tblContractualAgreements.[Expenses Terms], tblContractualAgreements.[Nature of Agreement Terms], tblContractualAgreements.[Confidentiality Terms], tblContractualAgreements.Definitions, tblContractualAgreements.[Export Lead Terms], tblContractualAgreements.[Delivarables Terms], tblContractualAgreements.[Support Terms], tblContractualAgreements.[Service Level Terms], tblContractualAgreements.[Response Times], tblContractualAgreements.[Entire Agreement and Governing Law], tblContractualAgreements.Proj
ectTypeID , tblContractualAgreements.[Contract Start Date], tblContractualAgreements.[Contract End Date], tblContractualAgreements.[Link 1], tblContractualAgreements.[Link 2]
FROM tblContractualAgreements
WHERE (((tblContractualAgreements.[Contract End Date])=Date()));"

Update_Exit:
Exit Function

Update_Err:
MsgBox Error$
Resume Update_Exit

End Function


Thanks
Hayley

[This message has been edited by Hayley Baxter (edited 03-08-2002).]

[This message has been edited by Hayley Baxter (edited 03-08-2002).]

 

[Rich]

The easiest way to get the syntax correct is to copy the SQL statement from a query, also although it doesn't look like it the statement I posted is all on one line, if you want it to span multiple lines you have to add all the ampersands etc
HTH

 

[Rich]

Window clutter, in my case Pat

 

[Hayley Baxter]

So far I have created a module called FunctionUpdate copied the code at the beginning and end of the sql statement. I have copied and pasted my sql statement from my query but whenever I do this my SELECT and WHERE lines show in red, I do have them all on one line except some words are carried over because my line is too long so if this is what's causing the error what do I need to do for a new line? I tried this_ but didn't like it.

Also when you are calling this procedure from the autoexec macro am I correct in saying it has to be called a different name from the function procedure itself and this should be defined by using the builder? Why then when I use the builder I can see two of my stored modules but not the new function one I just created? but yet it is saved and I can see it when I go into view the code!

Pat sorry but what do you mean by Querydef? Is this just a normal query or more than this and what exactly will it to and lastly how can it benefit me?

Thanks
Hayley

[This message has been edited by Hayley Baxter (edited 03-11-2002).]

 

[cable]

Change your autoexec macro to point to the function (Update) in a module (anyname cept Update).

 

[simongallop]

For the SQL code if the code goea onto another line you need to tell VBA that the following line is a continuation of the code and not a new instruction. ie for the string:

"My code over many lines"

Would be:

"My Code " & _
"over ma" & _
"ny lines"

HTH

[This message has been edited by Harry (edited 03-11-2002).]

 

[Hayley Baxter]

Got this all setup now but still having problems troubleshooting my code I have tried using &_ for a new line but nothing. Also I can't see how the syntax can be wrong when I copied and pasted the sql statement from both the queries in question. Here's what I have:

Function Update()
On Error GoTo Update_Err
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblArchive ( [Supplier ID], ContractTypeID, [Warranty Terms], [Ownership Terms], [Insurance Terms], [Termination Terms], [Liability Terms], [Professional Indemnity Terms], [Intelectual Property Rights Terms], [Disaster Recovery Terms], [Rates Terms], [Expenses Terms], [Nature of Agreement Terms], [Confidentiality Terms], Definitions, [Export Lead Terms], [Delivarables Terms], [Support Terms], [Service Level Terms], [Response Times], [Entire Agreement and Governing Law], ProjectTypeID, [Contract Start Date], [Contract End Date], [Link 1], [Link 2] )"
SELECT tblContractualAgreements.[Supplier ID], tblContractualAgreements.ContractTypeID, tblContractualAgreements.[Warranty Terms], tblContractualAgreements.[Ownership Terms], tblContractualAgreements.[Insurance Terms], tblContractualAgreements.[Termination Terms], tblContractualAgreements.[Liability Terms], tblContractualAgreements.[Professional Indemnity Terms], tblContractualAgreements.[Intelectual Property Rights Terms], tblContractualAgreements.[Disaster Recovery Terms], tblContractualAgreements.[Rates Terms], tblContractualAgreements.[Expenses Terms], tblContractualAgreements.[Nature of Agreement Terms], tblContractualAgreements.[Confidentiality Terms], tblContractualAgreements.Definitions, tblContractualAgreements.[Export Lead Terms], tblContractualAgreements.[Delivarables Terms], tblContractualAgreements.[Support Terms], tblContractualAgreements.[Service Level Terms], tblContractualAgreements.[Response Times], tblContractualAgreements.[Entire Agreement and Governing Law], tblContractualAgreements.Pro
jectTypeID , tblContractualAgreements.[Contract Start Date], tblContractualAgreements.[Contract End Date], tblContractualAgreements.[Link 1], tblContractualAgreements.[Link 2]
FROM tblContractualAgreements
WHERE (((tblContractualAgreements.[Contract End Date])=Date()));"
DoCmd.RunSQL "DELETE tblContractualAgreements.*, tblContractualAgreements.[Contract End Date]"
FROM tblContractualAgreements
WHERE (((tblContractualAgreements.[Contract End Date])=Date()));"


Update_Exit:
Exit Function

Update_Err:
MsgBox Error$
Resume Update_Exit

End Function


Part of the Select line is in red(when I reach the end of the line for coding in vb)

Both WHERE lines are in red(not at end of line) I am getting a syntax error and don't know how to resolve this. Can anyone see what's wrong with what I have above?

Many thanks
Hayley

 

[Dembrey]

That's some string. I copied/pasted this into Access and put in various "& _" and added some quotes until I got no redlines. Here it is, hope it works for you.

Function Update()
On Error GoTo Update_Err
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblArchive" & _
"( [Supplier ID], ContractTypeID, [Warranty Terms], [Ownership Terms], [Insurance Terms], " & _
"[Termination Terms], [Liability Terms], [Professional Indemnity Terms], [Intelectual Property Rights Terms], " & _
"[Disaster Recovery Terms], [Rates Terms], [Expenses Terms], [Nature of Agreement Terms], [Confidentiality Terms], " & _
"Definitions, [Export Lead Terms], [Delivarables Terms], [Support Terms], [Service Level Terms], [Response Times], " & _
"[Entire Agreement and Governing Law], ProjectTypeID, [Contract Start Date], [Contract End Date], [Link 1], [Link 2] ) " & _
"SELECT tblContractualAgreements.[Supplier ID], tblContractualAgreements.ContractTypeID, " & _
"tblContractualAgreements.[Warranty Terms] , tblContractualAgreements.[Ownership Terms], " & _
"tblContractualAgreements.[Insurance Terms], tblContractualAgreements.[Termination Terms], " & _
"tblContractualAgreements.[Liability Terms], tblContractualAgreements.[Professional Indemnity Terms], " & _
"tblContractualAgreements.[Intelectual Property Rights Terms], tblContractualAgreements.[Disaster Recovery Terms], " & _
"tblContractualAgreements.[Rates Terms], tblContractualAgreements.[Expenses Terms], " & _
"tblContractualAgreements.[Nature of Agreement Terms], tblContractualAgreements.[Confidentiality Terms], " & _
"tblContractualAgreements.Definitions, tblContractualAgreements.[Export Lead Terms], " & _
"tblContractualAgreements.[Delivarables Terms], tblContractualAgreements.[Support Terms], " & _
"tblContractualAgreements.[Service Level Terms], tblContractualAgreements.[Response Times], " & _
"tblContractualAgreements.[Entire Agreement and Governing Law], tblContractualAgreements.ProjectTypeID, " & _
"tblContractualAgreements.[Contract Start Date], tblContractualAgreements.[Contract End Date], " & _
"tblContractualAgreements.[Link 1], tblContractualAgreements.[Link 2]" & _
"FROM tblContractualAgreements WHERE (((tblContractualAgreements.[Contract End Date])=Date()));"

DoCmd.RunSQL "DELETE tblContractualAgreements.*, tblContractualAgreements.[Contract End Date]" & _
"FROM tblContractualAgreements WHERE (((tblContractualAgreements.[Contract End Date])=Date()));"

Update_Exit:
Exit Function

Update_Err:
MsgBox Error$
Resume Update_Exit

End Function

 

[Hayley Baxter]

Dembrey you're an absolute star!

Thank you so much all working and thanks to everyone else as well you've all been a great help!

Cheers
Hayley

 

[Hayley Baxter]

This is working great but I have now added a new field to my table called Forje Majure. I copied this from my sql like the rest(no red lines) when I paste into vb, however when I run the macro I get errors - diff ones as well each time I make a copy of my db and try to implement this one more field. I've had syntax error, values don't match ie fields no's are not the same from where I am appending and deleting. It's all working fine when I view it in the query running the macro generates the problems.

How bad am I I've got something working perfectly - I want to add one more field - and then it all goes wrong!!

Thanks for any help
Hayley

 

[Hayley Baxter]

All sorted now.