Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-11-2018, 08:41 AM   #1
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Update query in Access VBA pulling from SQL Server error

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.

cage4000 is offline   Reply With Quote
Old 04-11-2018, 10:19 PM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Update query in Access VBA pulling from SQL Server error

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![TheFieldName] & " as rs " _         
& "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 04-11-2018, 10:35 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,613
Thanks: 89
Thanked 1,494 Times in 1,410 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Update query in Access VBA pulling from SQL Server error

A recordset cannot be joined to tables in a query.

Galaxiom is offline   Reply With Quote
Old 04-12-2018, 08:16 AM   #4
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

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?
cage4000 is offline   Reply With Quote
Old 04-12-2018, 09:44 AM   #5
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 242
Thanks: 1
Thanked 69 Times in 67 Posts
bastanu is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

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
bastanu is offline   Reply With Quote
Old 04-12-2018, 04:09 PM   #6
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

I've never done that. how would you store a procedure in SQL server and call it?
cage4000 is offline   Reply With Quote
Old 04-12-2018, 08:48 PM   #7
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Update query in Access VBA pulling from SQL Server error

Quote:
Originally Posted by cage4000 View Post
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] & " AS rs " _
        & "ON ADJ_SUBtemp.ADJ_ID = rs.TRANS_ID;"

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 04-13-2018, 03:18 PM   #8
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

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?
cage4000 is offline   Reply With Quote
Old 04-13-2018, 05:02 PM   #9
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 242
Thanks: 1
Thanked 69 Times in 67 Posts
bastanu is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

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
bastanu is offline   Reply With Quote
The Following User Says Thank You to bastanu For This Useful Post:
cage4000 (04-16-2018)
Old 04-16-2018, 03:16 AM   #10
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

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()?
cage4000 is offline   Reply With Quote
Old 04-16-2018, 03:20 AM   #11
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,421
Thanks: 162
Thanked 1,735 Times in 1,705 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Update query in Access VBA pulling from SQL Server error

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 04-16-2018, 05:16 AM   #12
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

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.
cage4000 is offline   Reply With Quote
Old 04-16-2018, 05:18 AM   #13
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,421
Thanks: 162
Thanked 1,735 Times in 1,705 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Update query in Access VBA pulling from SQL Server error

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;"
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
cage4000 (04-16-2018)
Old 04-16-2018, 05:45 AM   #14
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

OMG it works! thank you guys so much for all your help!
cage4000 is offline   Reply With Quote
Old 04-16-2018, 06:38 AM   #15
bastanu
Registered User
 
Join Date: Apr 2010
Location: Kelowna, Canada
Posts: 242
Thanks: 1
Thanked 69 Times in 67 Posts
bastanu is on a distinguished road
Re: Update query in Access VBA pulling from SQL Server error

Great to see you solved it!

Cheers,
Vlad

bastanu is offline   Reply With Quote
Reply

Tags
dao.recordset , sql server , update query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update to error 3146 ODBC--call failed. SQL Server Linked Tables Rx_ Modules & VBA 5 06-18-2019 04:33 PM
OLE Server Error from List Box Update Cotty42 Forms 2 06-03-2014 01:19 AM
Update query not pulling criteria from form Chris115 Forms 17 07-19-2013 06:41 AM
Object Required error when pulling query data into form control skwilliams Forms 2 09-24-2009 03:27 AM
[SOLVED] Report Error when pulling info from Crosstab Query AnAngelnside Reports 2 05-20-2002 06:59 AM




All times are GMT -8. The time now is 07:16 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World