Error when reading data from a newly created table

[vanishboy]

I've created a vb.net program that connects to an access database.

Firstly, you can choose the database you want to connect to from a combobox. There's also the ability to create a new access database (button New) which is a copy of a template access database. It copies the template, gives it a new name (which you choose) and places it in a new subfolder. So the new db is exactly the same as the template (except its location). In the db, there's a table 'Userlogin'. It contains users who are allowed to use the program.

So you've created the new DB. Now You choose it, and you will connect to it.

After the 'choose database' dialog box, you get a 'login' dialog box. Here you can enter your user name & password (which is in the Userlogin table in the DB you just copied). Now the problem is, the following error appears:
'The Microsoft Jet-database-engine can not locate the table or query Userlogin. Make sure this exists and that the name is spelled correctly.'
It exists, is spelled correctly, location, ... everything is correct, but still the error appears.
When you close te program and run it again, the error won't appear. So this only happens when you've just created the new db (made a copy of the template). After that, it works correctly.

So a solution could be that te user should close the program first before using the db. But I find that rather ridiculous. It should work right away.

Anyone who knows a solution for this? Thanks for any help.

 

[Dimandja]

You mean your program is not working as expected?
Any code?

 

[Dimandja]

Before attempting to access the new database, run a quick check to see if your program can actually see the new database location:

Dim fso
Dim folder As String
folder = "C:\New" ' change to match the new folder path
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(folder) Then
...
End If

 

[vanishboy]

Dim fso as ...

What should this be declared as?

 

[Dimandja]

There is no need for AS.

 

[vanishboy]

It recognizes the folder.

The program works perfectly when you first close the program and run it again. So the code is correct. Maybe there's need for a refresh? It's strange, because the database with its data exists.

 

[Dimandja]

You have a lot of faith in your code; who doesn't? But, if the code is not doing what you expect, then it is not correct: it's got a bug. Short of seeing your code, how can we help you?

 

[vanishboy]

I discovered that the problem is not only with a newly created database, but also if a database is being removed. So when any action is performed on databases.

So when you've performed an an action (create or remove), and you want to login immediately after (without restarting the program) the following message appears:
'The Microsoft Jet-database-engine can not locate the table or query Userlogin. Make sure this exists and that the name is spelled correctly.'

This is the code for the form when you want to connect to a database (something wrong in here? when you restart the program it works perfectly):


Option Strict On
Option Explicit On

Imports System.Text
Imports System.Data.OleDb
Imports System.IO

Public Class frm_DBKeuze
Inherits System.Windows.Forms.Form

Private dsDatabase As DataSet
Private dtDatabase As DataTable
Private drDatabase As DataRow

Private cnDummy As New OleDbConnection


#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnOk As System.Windows.Forms.Button
Friend WithEvents btnAfsluiten As System.Windows.Forms.Button
Friend WithEvents btnNieuw As System.Windows.Forms.Button
Friend WithEvents cboDatabase As System.Windows.Forms.ComboBox
Friend WithEvents btnVerwijder As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frm_DBKeuze))
Me.cboDatabase = New System.Windows.Forms.ComboBox
Me.btnOk = New System.Windows.Forms.Button
Me.btnAfsluiten = New System.Windows.Forms.Button
Me.btnNieuw = New System.Windows.Forms.Button
Me.btnVerwijder = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'cboDatabase
'
Me.cboDatabase.Location = New System.Drawing.Point(24, 32)
Me.cboDatabase.Name = "cboDatabase"
Me.cboDatabase.Size = New System.Drawing.Size(240, 21)
Me.cboDatabase.TabIndex = 1
'
'btnOk
'
Me.btnOk.Location = New System.Drawing.Point(48, 80)
Me.btnOk.Name = "btnOk"
Me.btnOk.TabIndex = 2
Me.btnOk.Text = "OK"
'
'btnAfsluiten
'
Me.btnAfsluiten.DialogResult = System.Windows.Forms.DialogResult.Cancel
Me.btnAfsluiten.Location = New System.Drawing.Point(160, 80)
Me.btnAfsluiten.Name = "btnAfsluiten"
Me.btnAfsluiten.TabIndex = 3
Me.btnAfsluiten.Text = "Afsluiten"
'
'btnNieuw
'
Me.btnNieuw.Location = New System.Drawing.Point(296, 16)
Me.btnNieuw.Name = "btnNieuw"
Me.btnNieuw.TabIndex = 5
Me.btnNieuw.Text = "Nieuw"
'
'btnVerwijder
'
Me.btnVerwijder.Location = New System.Drawing.Point(296, 56)
Me.btnVerwijder.Name = "btnVerwijder"
Me.btnVerwijder.TabIndex = 4
Me.btnVerwijder.Text = "Verwijder"
'
'frm_DBKeuze
'
Me.AcceptButton = Me.btnOk
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.CancelButton = Me.btnAfsluiten
Me.ClientSize = New System.Drawing.Size(392, 118)
Me.ControlBox = False
Me.Controls.Add(Me.btnVerwijder)
Me.Controls.Add(Me.btnNieuw)
Me.Controls.Add(Me.btnAfsluiten)
Me.Controls.Add(Me.btnOk)
Me.Controls.Add(Me.cboDatabase)
Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
Me.Name = "frm_DBKeuze"
Me.Text = "Selecteer een database"
Me.ResumeLayout(False)

End Sub

#End Region

#Region " Dataset - Datatable "
Private Sub MaakDataSet()

Dim sbQuery As New StringBuilder
Me.dsDatabase = New DataSet

With sbQuery
.Remove(0, sbQuery.Length)
.Append("SELECT * ")
.Append("FROM Databases ")
.Append("ORDER BY txtDatabaseNaam")
End With

Me.dtDatabase = g_VulDataAdapter(Me.dsDatabase, "Databases", _
sbQuery, Me.cnDummy)
End Sub
#End Region

#Region " Schermknoppen "
Private Sub btnAfsluiten_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnAfsluiten.Click
End
End Sub

Private Sub btnOk_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnOk.Click
MaakDataSet()

Dim dvDatabase As DataView = New DataView(Me.dtDatabase, _
"", "txtDatabaseNaam", DataViewRowState.CurrentRows)
Dim iRijTeller As Integer = -1

iRijTeller = dvDatabase.Find(CType(Me.cboDatabase.SelectedItem, String))

If Me.cboDatabase.SelectedIndex = -1 Then
MessageBox.Show("Selecteer een database!", _
"Database kiezen", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
Me.cboDatabase.Focus()
Else
g_LocationString = CType(Me.dtDatabase.Rows(iRijTeller). _
Item("txtDatabasePath"), String)
g_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & g_LocationString

Dim g_cnMM As New OleDbConnection(g_ConnectionString)

g_cnYS = g_cnMM

Me.Close()
End If
End Sub

Private Sub btnNieuw_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnNieuw.Click
Dim frm_DBNieuw As New frm_DBNieuw
frm_DBNieuw.StartPosition = FormStartPosition.CenterParent
frm_DBNieuw.ShowDialog()
frm_DBNieuw.Dispose()

Dim iTeller As Integer

MaakDataSet()
cboDatabase.Items.Clear()
For iTeller = 0 To Me.dtDatabase.Rows.Count - 1
cboDatabase.Items.Add _
(CType(Me.dtDatabase.Rows(iTeller).Item("txtDatabaseNaam"), String))
Next iTeller
End Sub

Private Sub btnVerwijder_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles btnVerwijder.Click
MaakDataSet()

Dim dvDatabase As DataView = New DataView(Me.dtDatabase, "", _
"txtDatabaseNaam", DataViewRowState.CurrentRows)
Dim iRijTeller As Integer = -1

If Me.cboDatabase.SelectedIndex = -1 Then
MessageBox.Show("Selecteer een database!", _
"Database kiezen", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
Me.cboDatabase.Focus()
Else
iRijTeller = dvDatabase.Find(CType(Me.cboDatabase.SelectedItem, String))
Dim intAntwoord As Integer
intAntwoord = MessageBox.Show("Weet u zeker dat u de database " & vbCrLf & _
CType(Me.cboDatabase.SelectedItem, String) & " wilt verwijderen?", _
"Database verwijderen", MessageBoxButtons.YesNo, _
MessageBoxIcon.Exclamation)
If intAntwoord = DialogResult.Yes Then
Try
File.Delete(CType(Me.dtDatabase.Rows(iRijTeller). _
Item("txtDatabasePath"), String))

Try
Dim strPathFile As String = CType(Me.dtDatabase.Rows(iRijTeller). _
Item("txtDatabasePath"), String)
Dim iSlashPositie As Integer = g_Laatste_Positie_Teken("\", strPathFile)
Dim iVerwijderen As Integer
iVerwijderen = (strPathFile.Length - iSlashPositie)
Dim strPath As String

strPath = strPathFile.Remove(iSlashPositie, iVerwijderen)

Directory.Delete(strPath)

Dim blnTest As Boolean = False
blnTest = g_Verwijder_Database(CType(Me.cboDatabase.SelectedItem, _
String), cnDummy)
If blnTest Then
MessageBox.Show("Verwijderen database gelukt!", _
"Database verwijderen", _
MessageBoxButtons.OK, _
MessageBoxIcon.Information)
End If
Catch ex As Exception
MessageBox.Show("Verwijderen map mislukt!", _
"Database verwijderen", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
Me.cboDatabase.Focus()
End Try
Catch ex As Exception
MessageBox.Show("Verwijderen database mislukt!", _
"Database verwijderen", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
Me.cboDatabase.Focus()
End Try
End If
End If

Dim iTeller As Integer

MaakDataSet()
cboDatabase.Items.Clear()
For iTeller = 0 To Me.dtDatabase.Rows.Count - 1
cboDatabase.Items.Add _
(CType(Me.dtDatabase.Rows(iTeller).Item("txtDatabaseNaam"), String))
Next iTeller
Me.cboDatabase.SelectedIndex = 0
End Sub
#End Region

Private Sub frm_DBKeuze_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
g_LocationString = "Databases\MainDatabase.mdb"
g_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & g_LocationString
Dim g_cnMM As New OleDbConnection(g_ConnectionString)
g_cnYS = g_cnMM

Dim iTeller As Integer

MaakDataSet()
cboDatabase.Items.Clear()
For iTeller = 0 To Me.dtDatabase.Rows.Count - 1
cboDatabase.Items.Add _
(CType(Me.dtDatabase.Rows(iTeller).Item("txtDatabaseNaam"), String))
Next iTeller
End Sub

End Class