Query doesn't work in Access but works in SQL

[barbola]

I have spent way too much time on this and really need help asap.

I have a table with dDate, AvgPrice and FXRate. I have another field "Factor" that is calculated based on the Weight value in th FactorTable table. I'm not sure how to call this query to run for a single record first of all.

Second, I have the query written in 3 places:
1. A SQL stored procedure (it works but I don't know the code to call it in Access)

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[SP_FactorCalc] AS
  update DailyPrice set Factor =
 ((AvgPrice * FXRate * FactorTable.Weight)/ AvgPrice / FXRate)	
 from FactorTable where Factor is null

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

2. I wrote it in query analyzer and it works.

update DailyPrice set Factor =
 ((AvgPrice * FXRate * FactorTable.Weight)/ AvgPrice / FXRate) from FactorTable where Factor is null

3. I copied it into an Access query, and it tells me there is a syntax (missing operator) error. I also tried using VBA code, same error.

Here is the VBA code (same error as Access query):

Private Sub FXRate_AfterUpdate()

    Dim strSQL As String
    strSQL = "update dbo_DailyPrice set dbo_DailyPrice.Factor =
 ((AvgPrice * FXRate * dbo_FactorTable.Weight)/ AvgPrice / FXRate)	from dbo_FactorTable WHERE (((dbo_DailyPrice.Factor) Is Null));"
    
    Debug.Print strSQL
    DoCmd.RunSQL strSQL

End Sub

If I just paste it into an Access query without the "From" statement, it prompts me for the dbo_FactorTable.Weight. If I add that table to the query, it tells me it needs an updateable query.

I'm so lost! Please help! Ideally I'd like the Sp to work, possibly in the after update method of the FX Rate, or maybe a trigger as someone suggested (although I don't know how to do this either).

This is a high priority project the owners require and I am so stuck, and our resourcs are very slim, and they won't hire any help for me. thank you.

 

[HarleyQuinn]

There are a few ways to run the SP.

You can do it using DAO and QueryDef's (faq705-2531)

You can also run it from VBA using ADO (reference to Microsoft ActiveX Data Objects X.0) and the ADODB.Command object (faq222-2067).

I prefer the ADO (but I'm a VB6 programmer )

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[PWise]

Another way would be as an Pass Through Qurery

either calling the SP or running the update Statement

 

[barbola]

The ODBC connection failed. I put this at the top of my vba code.

Do I need quotes or a path for the dsn name or anything?

Const c_CONN_STR = "ODBC;DSN='myDSNname';SRVR=server1;DATABASE='myDBname';UID='sa';PWD='xxx';"

?

Now for some reason my tables are not updateable.

 

[HarleyQuinn]

You don't need the single quotes in the string.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[barbola]

mmm nope

Option Compare Database

Const c_CONN_STR = "ODBC;DSN=_myDSN;SRVR=Server1;DATABASE=_MyDBName;UID=sa;PWD=xxxxx;"


Private Sub FXRate_AfterUpdate()

    Dim strConnect As String
    Dim strSQL As String
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim strErrMsg As String

           Set dbs = CurrentDb
            
           strConnect = c_CONN_STR
                         
            Set qdf = dbs.CreateQueryDef("")
            qdf.Connect = strConnect
            strSQL = "exec SP_FactorCalc"
            qdf.ReturnsRecords = False
            qdf.SQL = strSQL
            dbs.QueryTimeout = 2000 'Set timeout to 4 minutes so it doesn't timeout.
            qdf.ODBCTimeout = 1000
            qdf.Execute
            DoCmd.Hourglass False


End Sub

 

[HarleyQuinn]

It errors? Doesn't do anything? A little expansion would enable us to help you more.

Works fine on mine, have you copied the server from the Configure option for the DSN you're using in the ODBC Data Source Administrator?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[barbola]

have you copied the server from the Configure option for the DSN you're using in the ODBC Data Source Administrator"

It just says connection to _MyDBName failed and the VBA stops at the qdf.Execute line.

I'm not sure what you mean by configure option? We only have one SQL server and I put in the real sa password.

The DSN when I created it, automatically put it under C:\Program Files\Common Files\ODBC\Data Sources\_MyDBName.dsn

Does that matter?

 

[barbola]

Run-time error '3151'"
ODBC--connection to '_MyDBName' failed.

My dsn and my db have the same name for real. Does that cause a problem?

 

[barbola]

Const c_CONN_STR = "ODBC;Server=SQL Server=_temp1;SERVER=SQL1;DATABASE=_temp1;UID=sa;PWD=test;"

I was able to get past the rror by adding Server = SQL Server. Now it is giving me other sql updating the data kind of grief. I'll be back!