how can i fix this runtime error?

[mxo]

Hi all

pls check my code i been trying to connect to access database with without a wizard but i keep getting the same runtime error "System.Runtime.InteropServices.COMException'occurred in LineSystem.exe also see my code below:

Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class frmMain
Inherits System.Windows.Forms.Form

Private Const MAX_RECORDS As Integer = 10000
Private strConnect As String

Private Function GetFileName() As String
Dim NewFileName As String
Dim Newfile As OpenFileDialog = New OpenFileDialog()
Dim MyChoice As DialogResult

With Newfile
.Filter = "Access Files (*.mdb)|*.mdb|All Files (*.*)|*.*"
.FilterIndex = 1
.DefaultExt = "mdb"
.InitialDirectory = "C:\Documents and Settings\User\My Documents\Line.mdb"
.CheckFileExists = False
.ReadOnlyChecked = True
.Title = "Open Access Data File"
End With

MyChoice = Newfile.ShowDialog
If MyChoice = DialogResult.Cancel Then
NewFileName = ""
Else
NewFileName = Newfile.FileName
End If
If NewFileName.Length > 0 Then
Return NewFileName
End If
End Function

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim FileName As String
Dim AdoConn As New ADODB.Connection()
Dim MyCat As New ADOX.Catalog()
Dim tbl As ADOX.Table

FileName = GetFileName()
If FileName.Length = 0 Then
Exit Sub
End If
Me.Text = "SQL LineSystem: " & FileName
strConnect = "Provider=Microsoft.JetOLEDB.4.0;Password="""";UserID=Admin;Data Source="
strConnect &= FileName
AdoConn.Open(strConnect)
MyCat.ActiveConnection = AdoConn

For Each tbl In MyCat.Tables
lstTables.Items.Add(tbl.Name.ToString)
Next
AdoConn.Close()

End Sub

 

[TipGiver]

I see one logical error at:

If NewFileName.Length > 0 Then
Return NewFileName
End If


It does NOT always return a value. If the user presses Cancel then NewFileName = "" (length = 0 !). Try removing the If/else and let the return only.

 

[AndyLee100]

The error points to where the problem lies.

"InteropServices.COMException". It's a COM problem.

It is a problem with the line:-

AdoConn.Open(strConnect).

This is where it falls over. Your connection string is incorrect. See amended below. "JetOLEDB.4.0" should be "Jet.OLEDB.4.0".

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";"

Now I really enjoyed debugging this but it took me 5 mins to copy into a new project, debug and fix. Did you attempt to do any debugging at all?

Also I would suggest opening an application with a dialog box is probably not the best way to go about it.

Try this url for loads of connection strings

http://www.connectionstrings.com/

 

[TipGiver]

Nice catch! I didn't see the missing dot...

 

[AndyLee100]

Add this to your code to stop adding system tables:

If Not tbl.Type = "SYSTEM TABLE" And Not tbl.Type = "ACCESS TABLE" Then
lstTables.Items.Add(tbl.Name.ToString)
End If

Nice bit of code, think I will nick this !