I'm trying to create this SIMPLE VB application that essentially opens an excel file and row by row it sends pieces of the data to a SQL database to execute a stored proc. But its not working . I can loop through the data but once it gets to the adoConn.open it exits the function.

Here's my code. Can anyone see what I'm doing wrong or perhaps missing? Do you need any additional info?

Dim FileToOpen As String

Private Sub Form_Load()

    Dim MyXL As Excel.Application 'create MyXL
    Set MyXL = New Excel.Application
    Dim WkbkName As String

    On Error Resume Next
        Set MyXL = GetObject(, "Excel.Application")

        If Err.Number = 429 Then
            Set appExcel = CreateObject("Excel.Application")
        End If


    FileToOpen = Application.GetOpenFilename 'Opens up explorer

    If FileToOpen = "False" Or FileToOpen = "" Then
        MsgBox "Ensure file is chosen correctly"
    Else
        Workbooks.Open FileName:=FileToOpen 'Opens up the file selected
        'Places the portion of the filename I want into a string variable
        WkbkName = Mid$(FileToOpen, InStr(FileToOpen, "_") + 1, InStr(FileToOpen, "[") - InStr(FileToOpen, "_") - 1)
        Application.Visible = True 'in Excel
    End If


    'Re-Saves the workbook using the new filename (withOUT the characters that Excel doesn't accept)
    Application.ActiveWorkbook.SaveAs " " + WkbkName + " "

    Call CrossingFiles 'Calls function

    Application.ActiveWorkbook.Save
    Application.DisplayAlerts = False
    'Application.ActiveWorkbook.Close

    Set MyXL = Nothing

    Unload OpenFile

End Sub

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund, trans_type, security_id, shares As String
    Dim strSQL As String
    Dim adoConn As ADODB.Connection
    Set adoConn = New ADODB.Connection
    Set wsData = ActiveSheet

    wsData.Rows(1).Delete
                
    'This will use the new worksheet as the 'trading ground' and will create all of the values
    'which will then be copied over to the original D column
    Dim MyColumns_Range As Range
    Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown))
    For Each c In MyColumns_Range
        fund = Range("A1").Value
        trans_type = Range("B1").Value
        security_id = Range("C1").Value
        shares = Range("E1").Value

        strSQL = "EXECUTE ssga_import_residuals " & fund & ", " & trans_type & ", " & security_id & ", " & shares & ", ,"

        With adoConn
            .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Data Source=odbcuser;Initial Catalog=main"
            .Open
            .Execute strSQL, , adCmdText

        End With

    fund = " "
    trans_type = " "
    security_id = " "
    shares = " "
    Next c

    Set wsData = Nothing
    adoConn.Close

    'deletes the newly created worksheet and sets everything to original status
    'yet with new data
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub

The problem is that you have a 'on error resume next' in the caller method (form_load). When ado.open fails, the program flow returns to the caller.
Put a 'on error goto errhandler' in CrossingFiles, and print the error in errhandler, so you know what is going on. As an alternative, Tools->Options->General tab->Break on all errors. Or stop the debugger in the .open line, step by F10 and get the error in the Immediate window.

Marco

Ok it does make sense now. I changed my code a bit to display the error description. I also took the password out of my connection string so that it would used trusted connection (using my ODBC). I am now connecting. However my new problem deals with the string I am passing to the SQL server.

This is my sql string:

strSQL = "EXECUTE ssga_import_residuals " & fund & ", '" & trans_type & "', '" & security_id & "', " & shares & ", ,"

To pass it:

adoConn.Execute strSQL, , adCmdText

Yet I am getting an error:
"Incorrect syntax near ','"

Any ideas?

Hello
I have gone through your code. The One thing I found in your code is you need to check the Provider value in the connection string.

For your reference the connection string in your code is as follows.

.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=user;Data Source=odbcuser;Initial Catalog=main"
Actually the syntax of connection string is right but
SomeTimes The DOT (.) you are using within strings might be creating problems as in VB it is teated as very special char, go through the provider details first. Then you can try with string handling functions.