Update query in Access VBA pulling from SQL Server error (1 Viewer)

cage4000

Registered User.
Local time
Today, 12:48
Joined
Oct 14, 2015
Messages
49
Good Morning everone,

i'm having a real problem figuring this one out. what i'm trying to do is run a query to update a table on the SQL Server but it keeps erroring out. i have 2 ways i'm trying to accomplish this. if you can help me with a solution for at least one of them it will fix my issue. Also, all tables this is referencing is on the SQL Server (No Local tables in the access Frontend).

Attempt # 1

Code:
    Dim sqlGroupDif As String
    Dim sqlUp As String
    Dim db As DAO.Database
    Dim rsDif As DAO.Recordset
    
    Set db = OpenDatabase("", False, False, "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Uid=QALogIn;Pwd=qalogin;")
    


sqlGroupDif = "SELECT sum(Cost_per_Each - COST) as CostEach, TRANS_ID FROM tblTempAdjFromExclDif GROUP BY TRANS_ID"

Set rsDif = db.OpenRecordset(sqlGroupDif, dbOpenDynaset, dbSeeChanges)



sqlUp = "UPDATE ADJ_SUBtemp " _
        & "Set TotCostOff = rs.CostEach " _
        & "FROM ADJ_SUBtemp INNER JOIN " & rsDif & " as rs " _
        & "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"


db.Execute sqlUp, dbSeeChanges


when running this code it gives the error:

"Compile error: Type mismatch" and highlights "rsDif" in the sqlUp SQL statement.


Attempt # 2

Code:
    Dim sqlUp As String
    Dim db As DAO.Database
    Dim rsDif As DAO.Recordset
    
    Set db = OpenDatabase("", False, False, "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Uid=QALogIn;Pwd=qalogin;")
    

sqlUp = "UPDATE ADJ_SUBtemp " _
        & "Set TotCostOff = rs.CostEach " _
        & "FROM ADJ_SUBtemp INNER JOIN (SELECT sum(Cost_per_Each - COST) as CostEach, TRANS_ID FROM tblTempAdjFromExclDif GROUP BY TRANS_ID) rs " _
        & "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"


db.Execute sqlUp, dbSeeChanges

when running this code it gives the error:

Run-time error '3075'
Syntax error (missing operator) in query expression 'rs.Costrs.CostEach FROM ADJ_SUBtemp INNER JOIN (SELECT sum(Cost_per_Each - COST) as CostEach, TRANS_ID FROM tblTempAdjFromExclDif GROUP BY TRANS_ID) rs ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID'

any help is most appreciated.
 

JHB

Have been here a while
Local time
Today, 20:48
Joined
Jun 17, 2012
Messages
7,732
You need to select the field you want to use from rsDif.
Code:
sqlUp = "UPDATE ADJ_SUBtemp " _         
& "Set TotCostOff = rs.CostEach " _         
& "FROM ADJ_SUBtemp INNER JOIN " & rsDif[B][COLOR=Red]![TheFieldName][/COLOR][/B] & " as rs " _         
& "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:48
Joined
Jan 20, 2009
Messages
12,849
A recordset cannot be joined to tables in a query.
 

cage4000

Registered User.
Local time
Today, 12:48
Joined
Oct 14, 2015
Messages
49
Thank you JHB for your input. i tried entering your update but it now gives the error:

Run-Time error '3075' Syntax error(missing operator) in query expression 'rs.CostEach FROM ADJ_SUBtemp INNER JOIN 0.09 rs ON AJD_SUBtemp.ADJ_ID = rs.TRANS_ID

this is what the code looks like now:

Code:
sqlUp = "UPDATE ADJ_SUBtemp " _
        & "Set TotCostOff = rs.CostEach " _
        & "FROM ADJ_SUBtemp INNER JOIN " & rsDif![CostEach] & " rs " _
        & "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"


Also, thank you Galaxiom for your input as well. If joining a rcordset to a table in a query is not possible then how would you go about accomplishing this? what code would you use to go around this roadblock?
 

bastanu

AWF VIP
Local time
Today, 12:48
Joined
Apr 13, 2010
Messages
1,401
Why not do it in the SQL server itself? Setup your first view to calculate the sum then use that in a stored procedure to run the update. Once you have it simply call it from your Access front-end by using a pass-through query.

Cheers,
Vlad
 

cage4000

Registered User.
Local time
Today, 12:48
Joined
Oct 14, 2015
Messages
49
I've never done that. how would you store a procedure in SQL server and call it?
 

JHB

Have been here a while
Local time
Today, 20:48
Joined
Jun 17, 2012
Messages
7,732
Thank you JHB for your input. i tried entering your update but it now gives the error:

Run-Time error '3075' Syntax error(missing operator) in query expression 'rs.CostEach FROM ADJ_SUBtemp INNER JOIN 0.09 rs ON AJD_SUBtemp.ADJ_ID = rs.TRANS_ID

this is what the code looks like now:

Code:
sqlUp = "UPDATE ADJ_SUBtemp " _
        & "Set TotCostOff = rs.CostEach " _
        & "FROM ADJ_SUBtemp INNER JOIN " & rsDif![CostEach] & " rs " _
        & "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"
I don't know what exactly you're trying to do, but you've removed the "AS", (you had it in post #1).
Code:
sqlUp = "UPDATE ADJ_SUBtemp " _
        & "Set TotCostOff = rs.CostEach " _
        & "FROM ADJ_SUBtemp INNER JOIN " & rsDif![CostEach] & "[B][COLOR=Red] AS[/COLOR][/B] rs " _
        & "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"
 

cage4000

Registered User.
Local time
Today, 12:48
Joined
Oct 14, 2015
Messages
49
Thank you JHB but it still did not work. =( So i decided to transfer the SQL code to the SQL server and run it as a stored procedure in access VBA like you suggested bastanu. The only problem is that when i try to run it, it give me an error. Please see below:

Code to connect to the server (Tested and works):
Code:
Public Function sqlDB() As Database
    Static mCurrSqlDb As Database
    If mCurrSqlDb Is Nothing Then
        Set mCurrSqlDb = OpenDatabase("", False, False, "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Uid=QALogIn;Pwd=qalogin;")
        Debug.Print Now, "static mCurrSqlDB inited for ", hWndAccessApp
    End If
    Set sqlDB = mCurrSqlDb
End Function


Code used to exsocute the Stored Procedure:
Code:
Dim sqlUp As String

sqlUp = "EXEC UpADJ_SUBTempWithUpDCost"

sqlDB().Execute (sqlUp)

The error i get when i try to run the above code is: Run-time error '3129': Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

can anyone help with with this error?
 

bastanu

AWF VIP
Local time
Today, 12:48
Joined
Apr 13, 2010
Messages
1,401
Could I suggest a different approach to see if it works. Create a new pass-through query, set its ODBC connect string property to the one you have in your sqlDB function, add EXEC UpADJ_SUBTempWithUpDCost to its SQL property and save it (lets say you name it qsql_UpdateCost_SQL). Then you just call that in VBA like this:

Code:
Dim cnn As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentDb.QueryDefs("qsql_UpdateCost_SQL").Connect
cnn.Open cnn.ConnectionString


DoCmd.Hourglass True

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "UpADJ_SUBTempWithUpDCost"
cmd.CommandTimeout = 2400
cmd.Execute


DoCmd.SetWarnings True
DoCmd.Hourglass False


Set cmd = Nothing

Let us know if it works.

Cheers,
Vlad
 

cage4000

Registered User.
Local time
Today, 12:48
Joined
Oct 14, 2015
Messages
49
Thank you VLad, but this only works if i make a connection in using the ODBC data sources. unfortunately i need to stay away from making an ODBC connection or all of the PC's that will be running this will need to have there ODBC data sources updated and i do not have admin access to do so. is there any other way you can think of using the Public Function sqlDB()?
 

Minty

AWF VIP
Local time
Today, 19:48
Joined
Jul 26, 2013
Messages
10,355
No you don't, you can specify the ODBC connection string in the query or the VBA code and include the user name and password as you did in your initial post.

The end user never see's it unless they are examining your code.
 

cage4000

Registered User.
Local time
Today, 12:48
Joined
Oct 14, 2015
Messages
49
Thank you for the correction Minty. i have implemented what you and bastanu said but i'm still getting an error on the code. I think i'm screwing up on the ODBC connection string you said to use.
the only part i changed on the code was the CurrentDB to sqlDB() to have it point to the SQL server.
can you point out where i'm going wrong?

Code:
im cnn As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = sqlDB().QueryDefs("qsql_UpdateCost_SQL").Connect
cnn.Open cnn.ConnectionString


DoCmd.Hourglass True

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "UpADJ_SUBTempWithUpDCost"
cmd.CommandTimeout = 2400
cmd.Execute


DoCmd.SetWarnings True
DoCmd.Hourglass False


Set cmd = Nothing

the error it gives me is: Run-time error '3265' Item Not found in this collection.
 

Minty

AWF VIP
Local time
Today, 19:48
Joined
Jul 26, 2013
Messages
10,355
You aren't setting the connection correctly, try

Code:
cnn.ConnecttionString = "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Uid=QALogIn;Pwd=qalogin;"
 

cage4000

Registered User.
Local time
Today, 12:48
Joined
Oct 14, 2015
Messages
49
OMG it works! thank you guys so much for all your help!
 

Users who are viewing this thread

Top Bottom