Having trouble with OleDbConnection

Question

I'm working on a program that will go out to an access database, wipe out the records for all 4 of the tables that I specify, before creating a new table in the database that contains all of the data from my Excel "Table of Contents" file. I'm having trouble with the OleDbConnection side of things. It keeps erroring out on the line that contains conn.Open() with the error "Invalid Argument"

My code is as follows:

Private Sub btnAccess_Click(sender As System.Object, e As System.EventArgs) Handles btnAccess.Click
    Dim AccessPath As String = ""
    Dim com1 As OleDbCommand
    Dim com2 As OleDbCommand
    Dim com3 As OleDbCommand
    Dim com4 As OleDbCommand
    Dim DatabaseFile As String = ""
    Dim DatabaseFileTitle As String = ""
    Dim ExcelFile As String = ""
    Dim ExcelFileTitle As String = ""
    Dim connect As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & _
";Extended Properties=Excel 12.0;"

    OpenFileDialog2.DefaultExt = "*.accdb"
    MsgBox("Select the ACCESS FILE from the working directory.", , "BROWSE FOR THE ACCESS DATABASE")

    OpenFileDialog2.InitialDirectory = Microsoft.VisualBasic.FileIO.FileSystem.CurrentDirectory
    'Text (*.txt)|*.txt|Pictures (*.bmp;*.ico)|*.bmp;*.ico
    OpenFileDialog2.Filter = "Access Database (*.accdb)|*.accdb"
    OpenFileDialog2.Title = "Select ACCESS DATABASE File."
    OpenFileDialog2.ShowDialog()
    DatabaseFile = OpenFileDialog2.FileName
    DatabaseFileTitle = System.IO.Path.GetFileNameWithoutExtension(OpenFileDialog2.FileName)

    OpenFileDialog3.DefaultExt = "*.xlsx"
    MsgBox("Select the EXCEL TOC FILE from the working directory.", , "BROWSE FOR THE EXCEL TOC")

    OpenFileDialog3.InitialDirectory = Microsoft.VisualBasic.FileIO.FileSystem.CurrentDirectory
    'Text (*.txt)|*.txt|Pictures (*.bmp;*.ico)|*.bmp;*.ico
    OpenFileDialog3.Filter = "Excel Spreadhseet (*.xlsx)|*.xlsx"
    OpenFileDialog3.Title = "Select EXCEL TOC File."
    OpenFileDialog3.ShowDialog()
    ExcelFile = OpenFileDialog3.FileName
    ExcelFileTitle = System.IO.Path.GetFileNameWithoutExtension(OpenFileDialog3.FileName)

    Using conn As New OleDbConnection(connect)
        Using cmd As New OleDbCommand()
            cmd.Connection = conn
            cmd.CommandText = "SELECT * INTO [MS Access;Database=" & DatabaseFile & "].[New Table] FROM [Sheet1$]"
            conn.Open()
            cmd.ExecuteNonQuery()
            com1 = New OleDbCommand("delete from tblArtId", conn)
            com2 = New OleDbCommand("delete from tblFigure", conn)
            com3 = New OleDbCommand("delete from tblSubGroups", conn)
            com4 = New OleDbCommand("delete from tblGroups", conn)
            com1.ExecuteNonQuery()
            com2.ExecuteNonQuery()
            com3.ExecuteNonQuery()
            com4.ExecuteNonQuery()
                conn.Close()

        End Using
       End Using
   MsgBox("Records Deleted")
End Sub

Answer 1

If anyone is curious....my updated code is as follows:

    Private Sub btnAccess_Click(sender As System.Object, e As System.EventArgs) Handles btnAccess.Click
    Dim AccessPath As String = ""
    Dim com1 As OleDbCommand
    Dim com2 As OleDbCommand
    Dim com3 As OleDbCommand
    Dim com4 As OleDbCommand
    Dim DatabaseFile As String = ""
    Dim DatabaseFileTitle As String = ""
    Dim ExcelFile As String = ""
    Dim ExcelFileTitle As String = ""


    OpenFileDialog2.DefaultExt = "*.accdb"
    MsgBox("Select the ACCESS FILE from the working directory.", , "BROWSE FOR THE ACCESS DATABASE")

    OpenFileDialog2.InitialDirectory = Microsoft.VisualBasic.FileIO.FileSystem.CurrentDirectory
    'Text (*.txt)|*.txt|Pictures (*.bmp;*.ico)|*.bmp;*.ico
    OpenFileDialog2.Filter = "Access Database (*.accdb)|*.accdb"
    OpenFileDialog2.Title = "Select ACCESS DATABASE File."
    OpenFileDialog2.ShowDialog()
    DatabaseFile = OpenFileDialog2.FileName
    DatabaseFileTitle = System.IO.Path.GetFileNameWithoutExtension(OpenFileDialog2.FileName)

    OpenFileDialog3.DefaultExt = "*.xlsx"
    MsgBox("Select the EXCEL TOC FILE from the working directory.", , "BROWSE FOR THE EXCEL TOC")

    OpenFileDialog3.InitialDirectory = Microsoft.VisualBasic.FileIO.FileSystem.CurrentDirectory
    'Text (*.txt)|*.txt|Pictures (*.bmp;*.ico)|*.bmp;*.ico
    OpenFileDialog3.Filter = "Excel Spreadhseet (*.xlsx)|*.xlsx"
    OpenFileDialog3.Title = "Select EXCEL TOC File."
    OpenFileDialog3.ShowDialog()
    ExcelFile = OpenFileDialog3.FileName
    ExcelFileTitle = System.IO.Path.GetFileNameWithoutExtension(OpenFileDialog3.FileName)

    Dim connectExcel As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & _
";Extended Properties=Excel 12.0;"

    Dim connectAccess As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabaseFile & ";"

    Using connExcel As New OleDbConnection(connectExcel)
        Using cmd As New OleDbCommand()
            cmd.Connection = connExcel
            cmd.CommandText = "SELECT * INTO [MS Access;Database=" & DatabaseFile & "].[New Table] FROM [TOC Output for Excel$]"
            connExcel.Open()
            cmd.ExecuteNonQuery()
            connExcel.Close()
        End Using
    End Using

    Using connAccess As New OleDbConnection(connectAccess)
        Using cmdAccess As New OleDbCommand()
            com1 = New OleDbCommand("delete from tblArtId", connAccess)
            com2 = New OleDbCommand("delete from tblFigure", connAccess)
            com3 = New OleDbCommand("delete from tblSubGroups", connAccess)
            com4 = New OleDbCommand("delete from tblGroups", connAccess)
            connAccess.Open()
            com1.ExecuteNonQuery()
            com2.ExecuteNonQuery()
            com3.ExecuteNonQuery()
            com4.ExecuteNonQuery()
            connAccess.Close()
        End Using
    End Using




    MsgBox("Staging Table has been created. All Records Deleted from Group, SubGroup, Figure, and ArtID tables")
End Sub