Query and Sql Server (1 Viewer)

[FoFa]

So using migration assistant, I migrated an (2002 format) data tables to SQL Server 2008.
Had to relink the tables using the proper ID.
When a macro opens an update query, I get an ODBC error.
Yet i can run the query by itself with no problems.
All table open just fine.
Security on the SQL side seems fine.

What am i missing?

 

[Banana]

What does the macro does?

Do you get the ODBC error as well if you do this via VBA as well? If so, interrogate the DAO.Errors collection to get all detailed errors you didn't get to see with UI's simple ODBC error popup.

 

[FoFa]

The macro does set warnings (off), openquery 2 update queries, Setwarnings (on) that's it.

The macro is called from a form close button event.

What do you mean by interrogate the DAO?

 

[Banana]

Try this snippet in VBA

Private Sub TestWhatIsWrong()

Dim e As DAO.Error

On Error GoTo Oops

CurrentDb.Execute "<name of your query>", dbFailOnError

Msgbox "Done."

Exit Sub

Oops:

If Err.Number = 3146 Then 'ODBC error
   For Each e in DAO.Errors
      Debug.Print e.Number & e.Description
   Next
Else
   MsgBox Err.Number & " " & Err.Description
End If

End Sub

See what this returns for you.

I also wonder if it's possible that "OpenQuery" is not appropriate for those queries and we may need a different macro actions, perhaps?

 

[FoFa]

Returns 3151 connection to SQL server xxxxx failed.

I looked at other macro actions, but none jumped out at me.

 

[Banana]

Well, I think that's the problem - you'll need to fix the connection so it works. Did it used to work, and if so, what was changed between then and now?

 

[gemma-the-husky]

can you open the linked tables directly?

 

[FoFa]

Yes I can open the linked tables directly, I can run the queries directly.
Only if I run them via the macro do they fail.

 

[gemma-the-husky]

your macro has 2 update queries

does the second one depend on the first

maybe the first doesnt have time to complete , and therefore the second one crashes

leave the warnings active, see if access tells you what the problem is.

 

[FoFa]

OK, weirdness.
I figured I would create a pass through query just to get a connection string and setup an auto-relink of the tables. Pass through query setup connection string fails (when you click the build button) with ODBC Fail. Before the builder opens.
Close that DB, create a new one, do the same thing, works fine.
Any Ideas?