MS Access to SQL Server connection issue

[malibu65k]

Hi All,

I have an Access to SQL Server connection issue. I run a process against the production db and it runs fine. I run it against the Development db and I get an ODBC connection failed error. Nothing changes in the code. Same code for both environments and exact same data(QUERY) being executed. The Dev is a duplicate of Prod. I have full access to both. I can open all the linked tables in access and run queries against them but when the process gets to the Execute statement is where it errors out.

Set DB = CurrentDb()
Set qd = DB.CreateQueryDef(vbNullString)

With qd
.Connect = CON
.SQL = sqlString
.ReturnsRecords = retRecords
.ODBCTimeout = 360
.Execute
End With

for Production the string is - Runs fine, writes records to the tables in the Production db

Con = ODBC;Description=ASP_Prod;DRIVER=SQL Server;SERVER=10.0.0.5;APP=Microsoft® Windows® Operating System;DATABASE=Prod_ASP

for Development the string is - errors out

Con = ODBC;Description=ASP_Dev;DRIVER=SQL Server;SERVER=10.0.0.5;APP=Microsoft® Windows® Operating System;DATABASE=Dev_ASP

everything in both string is correct. The person who manages the server and the database says there is nothing wrong and it must be me doing something wrong. I can't see it. Am I missing something?


Thanks!

 

[PHV]

it errors out
Which error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[malibu65k]

3146 ODBC-Call failed. Sorry, I thought it said connect failed. Is there a difference?