CODEWITHSUNDEEP
sql-serverms-accessvba
Remo Williams
Remo Williams

Reputation: 235

Access/VBA/SQL 3141 error

I have an Access form tied to a linked SQL Server table, and I'm trying to create a utility within access that creates new records in the SQL Server database from existing records; those of which match criteria from three selections in the Access form.

I'm not great with Access, SQL, or VBA, so that's cool, but here's what I have in VBA:

Dim strSQL As String

strSQL = _
"SELECT *" _
& " INTO #MyTempTable" _
& " FROM ACCTLive1.dbo.BUDGET_ACCESS_Data1" _
& " WHERE Project_ID = '" & Forms!Utilities!comboMoveProjIDPID & "' " _
& " AND [Year] = '" & Forms!Utilities!txtMoveProjIDSourceYear & "' " _
& " UPDATE #MyTempTable" _
& " SET [Year]  = '" & Forms!Utilities!txtMoveProjIDDestYear & "' " _
& " UPDATE #MyTempTable" _
& " SET Total_Amt = (([Total_Amt] * .02)+[Total_Amt])" _
& " INSERT INTO ACCTLive1.dbo.BUDGET_ACCESS_Data1" _
& " SELECT *" _
& " FROM #MyTempTable" _
& " DROP TABLE #MyTempTable"

CurrentDb.Execute strSQL, dbFailOnError

I get an error:

Run-time 3141 error: the SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I grabbed the SQL query from the Debug Watch, and ran it successfully from SSMS.

I apologize ahead of time, as I know most of these 3141 issues are due to commas in the wrong place, etc, but the fact that the query works fine from SSMS leads me to believe I'm missing something else.

Thank you!

Upvotes: 0

Views: 316

Answers (2)

Brad
Brad

Reputation: 12253

You're trying to do a SELECT INTO, and UPDATE, another UPDATE, and INSERT and a DROP TABLE on Access tables. And this executing in Access not SQL Server tables because CurrentDb.Execute runs against your accdb not whatever you may have linked (remember you can have any number of linked data sources so it's not making any guesses for you).

Access will only process one statement at a time. Access also doesn't support #Temp tables.

You can execute this in SQL Server as a Pass Through query using ADO and it'll work but you should parameterize it like other answers have suggested already. There are many ways to go about this (stored procedure, ADO pass through query, QueryDef pass through query).

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Turning this into a stored procedure would reap many benefits. Your code is more manageable and you would start separating this into a data layer and application layer.

Your code is far more complicated than it needs to be. There is no need for a temp table, just select the data you need in your insert statement. A much simplified version of this as a stored procedure would look something like this.

create procedure BUDGET_ACCESS_Data1_Insert
(
    @ProjectID int
    , @SourceYear int
    , @DestinationYear int
) as begin

    set nocount on;

    INSERT INTO ACCTLive1.dbo.BUDGET_ACCESS_Data1
    (
        Column1
        , Column2
        , Total_Amt
        , [Year]
    )
    SELECT Column1
        , Column2
        , Total_Amt * 1.02 --This is a simplified version. 
        , @DestinationYear
    FROM ACCTLive1.dbo.BUDGET_ACCESS_Data1
    WHERE Project_ID = @ProjectID
        AND [Year] = @SourceYear

end

Upvotes: 2

Related Questions