DoCmd Run SQL with Access as Automation Server

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I'm trying to run a SQL statement in Excel 2000 that runs
a make table query. I keep getting a syntax error
message. I started by copying the SQL from the SQL view
of the query in the Access database into an Excel code
module. Any ideas on how to adjust the copied SQL code
so that it will run the make table query from the Excel
module?
 
Joe said:
I'm trying to run a SQL statement in Excel 2000 that runs
a make table query. I keep getting a syntax error
message. I started by copying the SQL from the SQL view
of the query in the Access database into an Excel code
module. Any ideas on how to adjust the copied SQL code
so that it will run the make table query from the Excel
module?
Click to expand...

Please post the code you're using.
 
Sub RunGLPLUSF203Query()
Dim acApp As Access.Application
Set acApp = New Access.Application

acApp.OpenCurrentDatabase "P:\Departments\General
Accounting\Databases\GLPLUSF203-GL0003-BAL.mdb", False

DoCmd.RunSQL "DELETE tblManagedBalanceSheet.* FROM
tblManagedBalanceSheet;"

acApp.DoCmd.RunSQL "(INSERT INTO tblManagedBalanceSheet
(StatementType,BalSheet,Combo,BalanceSheetCategory,MFACCT,M
FDESC,FAC,FacilityName,Actual2002,CurMo,[Stmt&Acct#]))" & _
" SELECT [FacilityMaster]!
[StatementType] AS StatementType, [ChartOfAccounts]!
[BalSheet] AS BalSheet, [FacilityMaster]![StatementType] &
[ChartOfAccounts]![BalSheet] AS Combo, [tblNSGPTax-
balsheet]![BalanceSheetCategory] AS BalanceSheetCategory,
[GLPLUSF203_GL0003]![MFACCT] AS MFACCT,
[GLPLUSF203_GL0003]![MFDESC] AS MFDESC, [FacilityMaster]!
[FAC] AS FAC, [FacilityMaster]![FacilityName] AS
FacilityName, [GLPLUSF203_GL0003]![MFLY12] AS Actual2002,
[GLPLUSF203_GL0003]![MFCY10] AS CurMo, [FacilityMaster]!
[StatementType] & [ChartOfAccounts]![AccountNumber] AS
[Stmt&Acct#]" & _
" FROM ((GLPLUSF203_GL0003 INNER JOIN
ChartOfAccounts ON [GLPLUSF203_GL0003]![MFACCT] =
[ChartOfAccounts]![Acct#txt]) INNER JOIN FacilityMaster ON
([GLPLUSF203_GL0003]![MFCNTR] = [FacilityMaster]![FAC])
AND ([GLPLUSF203_GL0003]![MFCORP] = [FacilityMaster]!
[Corp])) INNER JOIN [tblNSGPTax-balsheet] ON
ChartOfAccounts.BalSheet = [tblNSGPTax-balsheet]!
[NSPGTax]" & _
" WHERE ((([GLPLUSF203_GL0003]!
[MFCORP]) = 'UHI') And (([FacilityMaster]![OL]) = 'M') And
(([GLPLUSF203_GL0003]![MFLY12] + [GLPLUSF203_GL0003]!
[MFCY10]) <> 0))" & _
" GROUP BY [FacilityMaster]!
[StatementType], [ChartOfAccounts]![BalSheet],
[FacilityMaster]![StatementType] & [ChartOfAccounts]!
[BalSheet], [tblNSGPTax-balsheet]![BalanceSheetCategory],
[GLPLUSF203_GL0003]![MFACCT], [GLPLUSF203_GL0003]![FDESC],
[FacilityMaster]![FAC], [FacilityMaster]![FacilityName],
[GLPLUSF203_GL0003]![MFLY12], [GLPLUSF203_GL0003]!
[MFCY10], [FacilityMaster]![StatementType] &
[ChartOfAccounts]![AccountNumber]" & _
" HAVING ((([FacilityMaster]!
[StatementType]) Not Like '100*' And ([FacilityMaster]!
[StatementType]) Not Like 'Greystone' And
([FacilityMaster]![StatementType]) Not Like 'Tandem') AND
(([GLPLUSF203_GL0003]![MFACCT]) Between '100000'
And '399999'));"

acApp.CloseCurrentDatabase
Set acApp = Nothing
End Sub
 
Sub RunGLPLUSF203Query()
Dim acApp As Access.Application
Set acApp = New Access.Application

acApp.OpenCurrentDatabase "P:\Departments\General
Accounting\Databases\GLPLUSF203-GL0003-BAL.mdb", False

DoCmd.RunSQL "DELETE tblManagedBalanceSheet.* FROM
tblManagedBalanceSheet;"

acApp.DoCmd.RunSQL "(INSERT INTO tblManagedBalanceSheet
(StatementType,BalSheet,Combo,BalanceSheetCategory,MFACCT,M
FDESC,FAC,FacilityName,Actual2002,CurMo,[Stmt&Acct#]))" & _
" SELECT [FacilityMaster]!
[StatementType] AS StatementType, [ChartOfAccounts]!
[BalSheet] AS BalSheet, [FacilityMaster]![StatementType] &
[ChartOfAccounts]![BalSheet] AS Combo, [tblNSGPTax-
balsheet]![BalanceSheetCategory] AS BalanceSheetCategory,
[GLPLUSF203_GL0003]![MFACCT] AS MFACCT,
[GLPLUSF203_GL0003]![MFDESC] AS MFDESC, [FacilityMaster]!
[FAC] AS FAC, [FacilityMaster]![FacilityName] AS
FacilityName, [GLPLUSF203_GL0003]![MFLY12] AS Actual2002,
[GLPLUSF203_GL0003]![MFCY10] AS CurMo, [FacilityMaster]!
[StatementType] & [ChartOfAccounts]![AccountNumber] AS
[Stmt&Acct#]" & _
" FROM ((GLPLUSF203_GL0003 INNER JOIN
ChartOfAccounts ON [GLPLUSF203_GL0003]![MFACCT] =
[ChartOfAccounts]![Acct#txt]) INNER JOIN FacilityMaster ON
([GLPLUSF203_GL0003]![MFCNTR] = [FacilityMaster]![FAC])
AND ([GLPLUSF203_GL0003]![MFCORP] = [FacilityMaster]!
[Corp])) INNER JOIN [tblNSGPTax-balsheet] ON
ChartOfAccounts.BalSheet = [tblNSGPTax-balsheet]!
[NSPGTax]" & _
" WHERE ((([GLPLUSF203_GL0003]!
[MFCORP]) = 'UHI') And (([FacilityMaster]![OL]) = 'M') And
(([GLPLUSF203_GL0003]![MFLY12] + [GLPLUSF203_GL0003]!
[MFCY10]) <> 0))" & _
" GROUP BY [FacilityMaster]!
[StatementType], [ChartOfAccounts]![BalSheet],
[FacilityMaster]![StatementType] & [ChartOfAccounts]!
[BalSheet], [tblNSGPTax-balsheet]![BalanceSheetCategory],
[GLPLUSF203_GL0003]![MFACCT], [GLPLUSF203_GL0003]![FDESC],
[FacilityMaster]![FAC], [FacilityMaster]![FacilityName],
[GLPLUSF203_GL0003]![MFLY12], [GLPLUSF203_GL0003]!
[MFCY10], [FacilityMaster]![StatementType] &
[ChartOfAccounts]![AccountNumber]" & _
" HAVING ((([FacilityMaster]!
[StatementType]) Not Like '100*' And ([FacilityMaster]!
[StatementType]) Not Like 'Greystone' And
([FacilityMaster]![StatementType]) Not Like 'Tandem') AND
(([GLPLUSF203_GL0003]![MFACCT]) Between '100000'
And '399999'));"

acApp.CloseCurrentDatabase
Set acApp = Nothing
End Sub
-----Original Message-----


Please post the code you're using.
Click to expand...
Click to expand...

You may well be able to do this whole thing via DAO without ever
creating an instance of Access. If you set a reference to the DAO
object library, you could write something like this:

Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine.OpenDatabase( _
"P:\Departments\General Accounting\Databases\" & _
"GLPLUSF203-GL0003-BAL.mdb")

strSQL = "DELETE * FROM tblManagedBalanceSheet"
db.Execute strSQL, dbFailOnError

strSQL = <corrected insert query -- see below>
db.Execute strSQL, dbFailOnError

db.Close
Set db = Nothing

Now, as for correcting the SQL statement, there are two things obviously
wrong with it. First, and possibly unimportant, your statement appears
to be enclosed in parentheses. At least, it opens with one, so if the
parentheses are balanced it's enclosed in parentheses and it needn't (or
shouldn't) be.

Second, you use this syntax over and over:

[TableName]![FieldName]

This is not correct -- you should be using the "dot" (.), not the "bang"
(!), like this:

[TableName].[FieldName]

It may well be that if you change the bang to a dot throughout the
statement, and remove the parentheses around the statement, the query
will succeed.

Note, by the way, that this is not a make-table query, but rather an
insert query -- the target table already exists.

Incidentally, as posted this statement:
DoCmd.RunSQL "DELETE tblManagedBalanceSheet.* FROM
tblManagedBalanceSheet;"
Click to expand...

must be qualified by with the acApp object. If you don't want to change
over to the straight-DAO approach I outlined above, be sure to change it
to this:

acApp.DoCmd.RunSQL _
"DELETE * FROM tblManagedBalanceSheet;"
 
Back
Top