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
 

http://www.connectionstrings.com/
 

Skip,

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

Actually made some comments in the VB forum.

Skip,

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

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

Please post the contents of the stored procedure.

Skip,

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

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
 

is this the same issue?

thread222-1681344: Adding an ado parameter to legacy vb stored procedure call
 

Skip,

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