[2008] 2 Questions: Access Database & Progress Bar

Question 1:
My Access File is Adding All my Courses that Contain More than 1 of into 1 Database File.

Example:

I have ICS4M1-01 4 Times in a File

It creates the new Database File (ICS4M1-01.mdb) & then suppose to add just 1 to Information but instead it will add 3 others to it, all with the same information.

How can I fix this?

The Code for all of it is below.

vb Code:

Option Strict On
Imports System.Data.OleDb
Imports System.IO
Module modImport
    Public Sub ImportCourse()
        With frmCourses
            If .ofdOpen.ShowDialog = DialogResult.OK Then
                Dim FileCopyName As String = Application.StartupPath & "\Data\Courses\" & Path.GetFileName(.ofdOpen.FileName.Remove(.ofdOpen.FileName.LastIndexOf("."))) & ".CSV"
                If File.Exists(FileCopyName) = True Then
                    File.Delete(FileCopyName) 'Temp
                Else
                    File.Copy(.ofdOpen.FileName, FileCopyName)
                    SQL = "SELECT * FROM " & .ofdOpen.FileName
                    Dim Path As String = IO.Path.GetFullPath(.ofdOpen.FileName.Remove(.ofdOpen.FileName.LastIndexOf("\")))
                    Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";")
                    Command = New OleDbCommand(SQL, Connection)
                    Command.Connection.Open()
                    Adapter.SelectCommand = Command
                    DS = New DataSet
                    Adapter.Fill(DS)
                    Command.Connection.Close()
                    Application.DoEvents()
                    If bImportCancel = False Then
                        For Each DRow As DataRow In DS.Tables(0).Rows
                            If .lstCourses.Items.Contains(DRow("Classes(Import If #1)")) = False Then
                                frmImport.Show()
                                Connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Data\Courses\" & DRow("Classes(Import If #1)").ToString() & ".mdb")
                                CreateCourse(Connection.ConnectionString)
                                SQL = "INSERT INTO [Information] (CourseCode, RoomNumber) VALUES (?, ?);"
                                Command = New OleDbCommand(SQL, Connection)
                                Command.Connection.Open()
                                Command.Parameters.AddWithValue("CourseCode", DRow("Classes(Import If #1)"))
                                Command.Parameters.AddWithValue("RoomNumber", DRow("Home Room/Form"))
                                Command.ExecuteNonQuery()
                                Connection.Close()
                                frmImport.pbImport.Maximum = DS.Tables(0).Rows.Count
                                If frmImport.pbImport.Value > DS.Tables(0).Rows.Count + 5 Then
                                    frmImport.pbImport.Value = frmImport.pbImport.Maximum
                                    File.Delete(FileCopyName)
                                    frmImport.Close()
                                    MessageBox.Show("Import Complete")
                                Else
                                    frmImport.pbImport.Increment(5)
                                End If
                            End If
                        Next
                        MsgBox("Done")
                    End If
                End If
            End If
        End With
    End Sub
End Module

Question 2:
My Progress Bar doesn't really work. I want it to increment while reading from the list and each time it reads one line of the file I want it to + 1 to the Value, and then once it reaches the End I want it to say Done, like in MsgBox("Done") but frankly, it doesn't, how would I go about incrementing to the process of reading line by line ?

1) Add "DISTINCT" to your SQL line, this will chop out repeated data and only pull unique rows:

SQL = "SELECT DISTINCT * FROM " & .ofdOpen.FileName

You're pulling all the columns though, so even if one of those columns have different data, you'll get repeat listings. Always pull only the data you need:

SQL = "SELECT DISTINCT CourseCode, RoomNumber FROM " & .ofdOpen.FileName

You should use a parameter for your tablename too:
SQL = "SELECT DISTINCT CourseCode, RoomNumber FROM ? "
...
Command.Parameters.AddWithValue("TableName", .ofdOpen.FileName)

2) Make a progress bar that has a Min = 0 and a Max = 100.
Next, get a count of the rows you're going to be adding:
total = DS.Tables(0).Rows.Count

Finally, make a counting variable and += 1 each loop.
Now, it's just simple math we all learned in 5th grade:

ProgressBar.Value = (count / total) * 100

Thanks Jenner :) You seem to be one with great knowledge also. Always helping me, and giving me tips on how to improve.

vb Code:

Total = DS.Tables(0).Rows.Count
                                Count += 1
                                If frmImport.pbImport.Value >= Total Then
                                    MessageBox.Show("Import Complete")
                                    frmImport.pbImport.Value = frmImport.pbImport.Maximum
                                    File.Delete(FileCopyName)
                                    frmImport.Close()
                                Else
                                    frmImport.pbImport.Value = (Count / Total) * 100
                                End If

For some reason, when it reaches the end, or near it, it just stops. Not showing messagfe box.

You're using the wrong value in your comparison.

pbImport.Value will only ever be between 0 and 100. Why? Because you're the math you use standardizes it between 0 and 100; which is perfect for a progress bar.

What happens if you got 200 rows, so Total = 200?

pbImport.Value will NEVER be >= Total.

What you should be checking is: Count >= Total, because Count will be between 1 and Total. As soon as it hits Total, it'll break to your end-routine. :)