Hey guys,

Here is my error :

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

It occurs when I am inserting records into SQL Server. Actually, it works just fine as long as I only insert like one or two thousand records. But, when I try to insert say 10,000 records, it busts. The records are in a loop and are being inserted one at a time. I am doing this in vba.

Here is the connection string:

Set cnn = New ADODB.Connection
    cnn.Open "Provider=sqloledb;" & _
    "Data Source=localhost;" & _
    "Initial Catalog=Customer;" & _
    "User Id=xxxxxxx;" & _
    "Password=xxxxx"


Any suggestions?


nikotromus

__________________
nikotromus

Can you post all of your code in the loop?

The problem as far as I can tell is not with the data. In fact, I have the exact same code working just fine with an Access database. I have tried different sets of data, and it always does the same thing. Around record 1970, it bombs out and gives the error message I mentioned above. It is almost like there is some sort of limit to how many times you can connect to the database. I have spent hours trying to figure out what is going on here.

Here is the code below:

Dim cnn As ADODB.Connection
Call GetCnn(cnn)
Dim strqry As String

'INSERT RECORD INTO CALL LIST
Dim rsAction As ADODB.Recordset
Set rsAction = New ADODB.Recordset
rsAction.ActiveConnection = cnn

DIM CtrNumberToPull As Double

Dim branch1 As Double
Dim branch2 As Double

branch1 = rsBranches("Branch")
branch2 = branch1

Dim intFile As Integer
intFile = FreeFile
Open CurDir & "\ErrorLog.Txt" For Append Shared As intFile

While Not rsBranches.EOF
     CtrNumberToPull = rsBranches("NumberToPull")
     While rsBranches("bank") = rsData("bank") And rsBranches ("Branch") = rsData("branch")
            'INSERT INTO CALL LIST WHILE NUMBER TO PULL IS GREATER THAN 0
            If CtrNumberToPull > 0 Then
               strqry = "INSERT INTO tbl_CallList ([TransNum], [Bank], [Branch], [Phone], [OfficialBranchName], [Address], [City], [Date_1]) " & _
                        " VALUES (" & rsData("TransNum") & " , '" & rsData("Bank") & "', " & _
                        " " & rsData("Branch") & ", '" & rsData("Phone") & "', " & _
                        " '" & Replace(rsData("OfficialBranchName"), "'", "''") & "','" & Replace(rsData("Address"), "'", "''") & "', " & _
                        " '" & Replace(rsData("City"), "'", "''") & "', '" & rsData("Date_1") & "')"
                        rsAction.Open (strqry)
            End If

            Write #intFile, rsData("bank"), rsData("Branch")
            rsData.MoveNext

            If rsData.EOF Then
               GoTo here
            End If

            CtrNumberToPull = CtrNumberToPull - 1
          Wend

       rsBranches.MoveNext

    Wend

here:

nikotromus

I figured it out. The connection I use above connect over and over and over again until the memory fills up and it crashes. I changed the connection to open until the loop ends, and then close at the end.

Wow, this is the first time i've ever had a question that didn't at least attempt to get answered on this site. Ya'll are slackin! :)

Thanks,

Ryan


nikotromus

I had the same error on a desktop application which was exporting out a large amount of records from a database to xml.

I changed from using tcp/ip to named pipes and the error stopped.

I would have liked to have done what you did but time didnt permit but its nice to know it works.

Mylin