access queries running through vba

[jimmyp]

hi

i have 3 basic queries in a database and i want to be able to run those when the database is opened. i have tried using VBA but get an error, this is what i have so far -

Sub run()
 
DoCmd.RunSQL "INSERT INTO [currency] ( currencyName, euroRate )" & _
"SELECT Template.Code, Template.[Rate vs EUR]" & _
"FROM Template " & _
"WHERE (((Template.Code) In ('GBP','USD','HKD')));"
 
DoCmd.RunSQL "INSERT INTO [currency] ([currencyName], [euroRate])" & _
" VALUES ('EUR', '1.0000');"
 
DoCmd.RunSQL "SELECT currency.currencyName, currency.euroRate, (currency!euroRate/currency_1 !euroRate) AS dollarRate " & _
"FROM [currency], [currency] AS currency_1 " & _
"WHERE (((currency_1.currencyName)='USD'));"
 
End Sub

the first 2 queries run fine, but then i get an error stating that 'A RunSQL action require an argument consisting of an SQL statement'

any help much appreciated

jimmyp

[si_the_geek]

It's this bit:
(currency!euroRate/currency_1 !euroRate)

Which should be:
(currency.euroRate/currency_1.euroRate)



As you are using more than one table (even tho they are the same table), you should specify how they are linked - otherwise you will get a cross-join (all rows in one table will show next to all rows of the other). In this case tho, I guess it is ok as it is.

[salvelinus]

.RunSql isn't used with SELECT statements; you'd need a recordset to do that in code. Alternatively, you can save the query in the Access db and then use .OpenQuery "qryFoo" instead. (I made this same mistake last week in answering another poster).

[jimmyp]

ah ok thanks for the info