Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

I have a SQL query that works in Access 2003 but will not in Access 2007

I upgraded to Access 2007 and I have a very basic query. I have listed it below:
Set rstTemp = SalesPipelineData.OpenRecordset( _
        "Select Pipeline.Sales_Exec," & _
        "Pipeline.Status, " & _
        "Pipeline.Qtr_Expected_to_Close, " & _
        "Pipeline.DA_Rating, " & _
        "Pipeline.TCV_$k, " & _
        "Pipeline.Service_Rev_$k, " & _
        "Pipeline.Sales_Comments, " & _
        "Pipeline.Next_Step_w_Dates " & _
        "FROM Events.dbo.Pipeline Pipeline Where " & _
        "Sales_no = '" & Me.SalesNumber & "'", _
          dbOpenSnapshot, dbReadOnly)
I am getting a Run-time error '3024': could not find Events.dbo. This worked and still works in Access 2003. Can anyone enlighten me as to what might have changed and how I can adjust the code for 2007 so that it works.
0
mlb3397
Asked:
mlb3397
  • 5
  • 4
  • 3
  • +1
1 Solution
 
dqmqCommented:
Does this have an SQL Server backend?  
What is the name of the table as it appears on the Table tab?
Can you open the table directly?
0
 
BadotzCommented:
Perhaps:

Set rstTemp = SalesPipelineData.OpenRecordset( _
        "Select P.Sales_Exec," & _
        "P.Status, " & _
        "P.Qtr_Expected_to_Close, " & _
        "P.DA_Rating, " & _
        "P.TCV_$k, " & _
        "P.Service_Rev_$k, " & _
        "P.Sales_Comments, " & _
        "P.Next_Step_w_Dates " & _
        "FROM Pipeline AS P Where " & _
        "P.Sales_no = '" & Me.SalesNumber & "'", _
          dbOpenSnapshot, dbReadOnly)

Not sure about those [TCV_$k] and [Service_Rev_$k] field names, yikes!
0
 
mlb3397Author Commented:
Yes I can open it directly, and this exact code works in Access 2003. When I upgraded to Access 2007 is when it stopped working.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
dqmqCommented:
Does this have an SQL Server backend?  
What is the name of the table as it appears on the Table tab?
0
 
mlb3397Author Commented:
Yes a SQL server backend and the table name is pipeline.
0
 
dqmqCommented:
>Yes a SQL server backend and the table name is pipeline.

The SQL server  table name is irrelevant.  What's important is the tabledef name known to Access.  You can get that name from the Tables tab or from the title bar when you open the table directly.  That name possibly changed during the conversion and it needs to match exactly.

So, if it truely is "pipeline", then change"

  "FROM Events.dbo.Pipeline Pipeline Where " & _

To

  "FROM Pipeline Where " & _
0
 
BadotzCommented:
Exactly as I suggested earlier...
0
 
mlb3397Author Commented:
I guess I need to restate my point. The code works as it is shown above in Access 2003 but does not in Access 2007.  Would the tabledef change when using one version access from using another?
0
 
dqmqCommented:
I wouldn't really expect it, but since no obvious reason for error 3024 stands out, I'm just trying to rule out any potential root causes of that message, irregardless of what might have happened during the Access upgrade.  

To my knowledge, periods are not allowed in the names of Access objects, which supports my (and Badotz's) suspicion that your tabledef has a different name.  

In the time I've taken to defend my question, you could have answered it and drank a cup of coffee.  Humor us if you must.
0
 
mlb3397Author Commented:
As it turns out is was something in my connection string causing the problem. In Access 2003 the following worked fine:
Const ConnectString = "Driver=SQL Server;Server=XX.XX.XX.XX;database=master;Uid=USERNAME;PWD=PASSWORD;"

I had to remove "database=master;" from the string. So now it looks like:
Const ConnectString = "Driver=SQL Server;Server=XX.XX.XX.XX;Uid=USERNAME;PWD=PASSWORD;"

An interesting side note, the ordinal positions for the fields in the item set used to start at 1 and now starts at 0.
0
 
BadotzCommented:
Good for you! Self-sufficiency is what we strive for here at EE. Looks like you get your own points!
0
 
dqmqCommented:
And you were still able to open the table directly?  Hmmm.... Anyway, I'm glad you tracked down the problem.  
0
 
Computer101Commented:
PAQed with points refunded (125)

Computer101
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now