VBA error message from ADODB connection

[hedgracer]

We have Access databases which we are converting from SQL Server 2005 back ends to SQL Server 2008 back ends for tables with the Access database as a front end. During the test conversion I ran a form which has a button and received the following error:

Run-time error'-2147467259 (800004005)':
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

When I click debug it goes to the following vba code:


strCnxn = set_connection()
Set cnxn = New ADODB.Connection

cnxn.Open strCnxn ***THIS IS THE CODE IT GOES TO****

The set_connection() is as follows in a module:

Public Function set_connection()

set_connection = "Provider='sqloledb';Data Source='TSQLINST1/TSQLINST1';" & _
"Initial Catalog='Actg';Integrated Security='SSPI';"

End Function

Is there something I need to do differently for SQL Server 2008? Any help is appreciated. Thanks.

Dave

 

[SkipVought]

http://www.connectionstrings.com/

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Actually made some comments in the VB forum.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hedgracer]

I already looked at this site and came away more confused than I already was before. Under the SQL Server 2008 area I actually don't see anything different than what I am doing in the function set_connection(). Am I looking at something wrong?

Dave

 

[SkipVought]

Please post the contents of the stored procedure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hedgracer]

I am not making it to the stored procedure. Here is the entire Access 2007 sub:


Private Sub cmdProcessCEMS_Click()
DoCmd.Hourglass True

Dim smonth As String
smonth = txtDate
smonth = Left(smonth, 2)


If chkBksNotClosed.Value = True Then

smonth = "12"
Else
smonth = txtDate
smonth = Left(smonth, 2)
End If

strCnxn = set_connection()
Set cnxn = New ADODB.Connection

cnxn.Open strCnxn
cnxn.CommandTimeout = 0
strSQL_Execute = "dbo.CEMS_Deletion"
cnxn.Execute strSQL_Execute, , adExecuteNoRecords

'set import file variables and set transfertext command
Dim dkg As Variant, str As String
dkg = Application.FileDialog(msoFileDialogFilePicker).Show
str = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)

DoCmd.TransferText acImportDelim, "CEMSImportSpecification", "CEMS_Prelim", str

strSQL_Execute = "dbo.CEMS_Creation " & smonth
' MsgBox strSQL_Execute
cnxn.Execute strSQL_Execute, , adExecuteNoRecords

Dim strDefaultPrinter As String
strDefaultPrinter = Application.Printer.DeviceName
Set Application.Printer = Application.Printers(strDefaultPrinter)

DoCmd.OpenReport "Base CEMS Report", acViewPreview



DoCmd.Hourglass False
End Sub

The statement "cnxn.Open strCnxn" is where the error message debug directs you to when you click OK when you are asked if you would like to debug. I don't think the stored procedure is the problem here since I am never touching it.

Dave

 

[SkipVought]

is this the same issue?

thread222-1681344


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}