How do I separate a .mdb table into separate tables based upon different values in one of the fields?

I need to create separate tables for each individual time step in a .mdb table.  A sample .mdb is attached.  I need to be able to first open the .mdb and select the table that I want to use (tblSurface), then create a new table containing all associated data with each different time step (TimeID).  So, for tblSurface, as part of the example database below, I need to have a new table created for each TimeID=1, TimeID=2, TimeID=3, TimeID=4, ....TimeID=n.  Any help on this would be appreciated!!!  I don't really know how to even start this!  

I will eventually add the resulting table(s) depending on what time step(s) I need to view to an ArcMap document to convert to a shapefile.
AvgProbMay2.mdb
jvs7829Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Alexandre SimõesConnect With a Mentor Manager / Technology SpecialistCommented:
I've detected a problem on the above code.
I was naming the files wrong.
I want them to be something like: New_AvgProbMay_TimeID5.mdb and inside I want to see only records with TimeID=5

The attached code is correct now.

Cheers,
Alex

Imports System.Data.OleDb
 
Public Class frmDBBrowser
 
    Private DBPath As String = Application.StartupPath() & "\DB\AvgProbMay2.mdb"
    Private NewDBPath As String = Application.StartupPath() & "\DB\New_AvgProbMay_TimeID{0}.mdb"
    Private MainConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";User Id=admin;Password=;"
    Private NewDBConnectionStringTemplate As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;"
    Private dtTimeIDs As DataTable
 
    Private Sub frmDBBrowser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim conn As New OleDbConnection(MainConnectionString)
        Dim comm As OleDbCommand = conn.CreateCommand()
        comm.CommandText = "SELECT DISTINCT TimeID FROM tblSurface"
        comm.CommandType = CommandType.Text
 
        dtTimeIDs = New DataTable()
        conn.Open()
        dtTimeIDs.Load(comm.ExecuteReader())
        conn.Close()
 
        Me.lbTimeIDs.DataSource = dtTimeIDs
        Me.lbTimeIDs.DisplayMember = "TimeID"
 
    End Sub
 
    Private Sub lbTimeIDs_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbTimeIDs.SelectedIndexChanged
        Dim conn As New OleDbConnection(MainConnectionString)
        Dim comm As OleDbCommand = conn.CreateCommand()
        comm.CommandText = "SELECT * FROM tblSurface WHERE TimeID = " & DirectCast(lbTimeIDs.SelectedItem, DataRowView).Row(0).ToString()
        comm.CommandType = CommandType.Text
 
        Dim dtRecords As New DataTable()
        conn.Open()
        dtRecords.Load(comm.ExecuteReader())
        conn.Close()
 
        Me.dgvSurfaceData.DataSource = dtRecords
 
    End Sub
 
    Private Sub btnCreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDB.Click
        Dim InsertQryStr As String = "INSERT INTO {0} SELECT * FROM tblSurface WHERE TimeID={1}"
 
        Dim thisDBPath As String
        Dim conn As New OleDbConnection
        Dim comm As OleDbCommand
 
        For i As Integer = 1 To Me.dtTimeIDs.Rows.Count
            thisDBPath = String.Format(NewDBPath, Me.dtTimeIDs.Rows(i)(0).ToString())
            System.IO.File.Copy(DBPath, thisDBPath)
 
            conn.ConnectionString = String.Format(NewDBConnectionStringTemplate, thisDBPath)
            comm = conn.CreateCommand()
            comm.CommandText = "DELETE FROM tblSurface WHERE TimeID<>" + Me.dtTimeIDs.Rows(i)(0).ToString()
            comm.CommandType = CommandType.Text
            conn.Open()
            comm.ExecuteNonQuery()
            conn.Close()
        Next
    End Sub
 
End Class

Open in new window

0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hi!

The way I see this I would do it creating a small application.
This application would list all the tables on the MDB. Selecting one table it could ask for the destination table name and the query to execute.

Based on the query you can Create the Table, create the columns (the same as on the source table) and insert the records.

I think this is the main idea...

I don't have much time now to do it for you but I can help you on some points if you get stuck.

Cheers,
Alex
0
 
CodeCruiserCommented:
Use the following queries and code in your vb.net app

dim dbadp as new OLEDBDataAdapater("Select Distinct TimeID from tblSurface", connectionstring)
dbadp.fill(dTable)
dbadp.dispose

For i as integer = 0 to dTable.Rows.Count - 1
      dbcmd.commandtext = "Create Table " & dTable.Rows(i).item(0) & " ....
      dbcmd.executenonquery()
Next

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Jim P.Commented:
Why create separate tables?

Just create queries with a WHERE clause and then use those for reports.

You can create them on the fly or have them pre-created depending on what you're doing.
SELECT tblSurface.RunNumber, tblSurface.TimeID, tblSurface.TimeValue, tblSurface.Date, tblSurface.ParticleID, tblSurface.x, tblSurface.y, tblSurface.WindSpeed, tblSurface.WindDirection, tblSurface.UCurrent, tblSurface.VCurrent, tblSurface.C1, tblSurface.C2, tblSurface.C3, tblSurface.C4, tblSurface.C5, tblSurface.C6, tblSurface.C7, tblSurface.C8, tblSurface.C9, tblSurface.C10, tblSurface.C11, tblSurface.C12, tblSurface.C13, tblSurface.C14, tblSurface.C15, tblSurface.C16, tblSurface.C17, tblSurface.C18, tblSurface.C19, tblSurface.C20, tblSurface.C21, tblSurface.C22, tblSurface.C23, tblSurface.C24, tblSurface.C25
FROM tblSurface
WHERE (((tblSurface.TimeID)=2));

Open in new window

0
 
jvs7829Author Commented:
I was going to create separate tables and let them sit in a directory folder and then the user can add whichever one they wish to ArcMap to be used to create a shapefile for viewing spatially.  I'm not quite sure how to read/open the original table yet.
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
How are you doing?
Need help?
0
 
jvs7829Author Commented:
I definitely need help on this!  It would be great if I could identify unique values for the field "TimeID" in the table "tblSurface" within the database "Results.mdb" and then have them listed in a drop-down/listbox for the user to select.  Each unique value would then correspond to an individual table with all the data for that TimeID from the original table.  I need them as individual tables to add only select ones to ArcMap through a user form.
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hi mate!

So, what you need is to create a new table based on the distinct values of a column on another table... Am I correct?

To get distinct values from a database you have the DISTINCT operand to use on a query.
You can execute something like this:
SELECT DISTINCT TimeID FROM tblSurface

Now to put these distinct values on a new table you can execute:
INSERT INTO tblTimes (TimeID) SELECT DISTINCT TimeID FROM tblSurface

Now, with this tblTimes table you can fill you listbox from here.
Also, a table with only ID's don't make much sense to me, mays adding a description column... I don't know the context of this.

Alex
0
 
jvs7829Author Commented:
I don't necessarily need to create a new table based on distinct values unless that's the best way to grab unique values and fill the listbox.  The individual tables need to be one for each unique TimeID.  Maybe this will help....I need to break up the tblSurface into individual tables for each TimeID.

NewTableTime1 (for TimeID=1)
RunNumber, TimeID, TimeValue,Date, ParticleID, etc....
x, 1, x, x, x, x, ....
x, 1, x, x, x, x,....

NewTableTime2 (for TimeID=2)
RunNumber, TimeID, TimeValue, Date, ParticleID, etc.....
x, 2, x, x, x, x,.....
x, 2, x, x, x, x,.....
x, 2, x, x, x, x,......

NewTableTime3 (for TimeID=3)
RunNumber, TimeID, TimeValue, Date, ParticleID, etc.....
x, 3, x, x, x, x, ................
x, 3, x, x, x, x, ................
x, 3, x, x, x, x, ................
x, 3, x, x, x, x, ................
x, 3, x, x, x, x, ................

NewTableTimen (for TimeID=n)
RunNumber, TimeID, TimeValue, Date, ParticleID, etc.....
x, n, x, x, x, x, ................
x, n, x, x, x, x, ................
x, n, x, x, x, x, ................
x, n, x, x, x, x, ................
x, n, x, x, x, x, ................


Listbox (all unique TimeID values1 through n)
1
2
3
.
.
.
n



0
 
Jim P.Commented:
Take a look at the Surface_Form in the upload.

It just filters off the original data -- no need to make separate tables.
Q24403397-AvgProbMay2.mdb
0
 
jvs7829Author Commented:
The main tblSurface is results from a model at different sequential time steps (TimeID).  I need separate tables so the end user can select a table for a specific TimeID from the listbox to load into ArcMap.  Then, some additional stuff will be done to the data in the individual time table to get it to look good for them.   It will be more confusing/difficult to use with queries I need to have the tables stored in a folder and the user pick which time they want to use for analyzing the results in ArcMap.  ArcMap will perform the manipulation on all the data in the main table unless I split it up into separate tables.
0
 
jvs7829Author Commented:
Code Cruiser, Are you there?  

How do I define dTable as a new database table???  Dim dTable As _______????  Also what should go in the "....."?

Thanks!!!
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
I'm doing you a project to do this.
I'm packing you my solution, this is a rar file, please rename the txt extension back to rar.

this isn't finished because I don't have time right know but you only need to finish the btnCreateDB_Click code.

I'll try to finish it shortly.

EE.ID24403397.txt
0
 
Jim P.Connect With a Mentor Commented:
Go to the modules tab -- new module. Then on the Tools --> References and find the Microsoft DAO 3.6 Object Library and check it on.

Then copy and paste the code below into the module window.

Put the cursor anywhere SplitOut_tblSurface code and hit <F5> to run it. You'll have the tables listed out.
Option Compare Database
Option Explicit
 
Public Function SplitOut_tblSurface()
 
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim SQL As String
 
Dim I As Integer
Dim TblName As String
 
SQL = "SELECT TimeID " & _
    "FROM tblSurface " & _
    "GROUP BY TimeID " & _
    "ORDER BY TimeID "
 
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)
 
If RS.EOF = False Then
    RS.MoveFirst
Else
    MsgBox "No Data", vbExclamation, "Exiting Function"
    Set RS = Nothing
    Set DB = Nothing
    Exit Function
End If
 
Do Until RS.EOF = True
    TblName = "tblSurface_TimeID_" & Format(RS!TimeID, "00")
    If TableExistence(TblName) = True Then
        DoCmd.SetWarnings False
        If IsTblLoaded(TblName) = True Then
            DoCmd.Close acTable, TblName, acSaveNo
        End If
        DoCmd.DeleteObject acTable, "eSafe_Log"
        DoCmd.SetWarnings True
    End If
    SQL = "SELECT * " & _
        "INTO " & TblName & " " & _
        "FROM tblSurface " & _
        "WHERE TimeID = " & RS!TimeID
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    RS.MoveNext
Loop
 
Set RS = Nothing
Set DB = Nothing
 
End Function
 
 
Public Function IsTblLoaded(ByVal strTableName As String) As Boolean
' Returns True if the specified form is open in Form view or _
  Datasheet view.
IsTblLoaded = False
Const conObjStateClosed = 0
Const conDesignView = 0
 
If SysCmd(acSysCmdGetObjectState, acTable, strTableName) <> conObjStateClosed Then
    IsTblLoaded = True
End If
 
End Function
 
 
Public Function TableExistence(TableName As String) As Boolean
 
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim SQL As String
 
TableExistence = False
 
SQL = "SELECT NAME " & _
        "FROM  MSysObjects " & _
        "WHERE TRIM(UCASE(NAME)) = '" & Trim(UCase(TableName)) & "';"
 
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)
 
If RS.EOF = True Then
    TableExistence = False
Else
    RS.MoveFirst
    If Trim(UCase(RS!Name)) = Trim(UCase(TableName)) Then
        TableExistence = True
    End If
End If
 
End Function

Open in new window

0
 
jvs7829Author Commented:
jimpen,

I'd love to try it out and it looks interesting, but I'm getting all sorts of errors.  Since you want to use the Microsoft DAO 3.6 Object Library, its a bit different from what I had.  I'm trying to learn from this, but its proving more frustrating than anything.

I've defined "CurrentDb" as a DAO.Database (not sure if this is right).  I'm not sure how to define these as strings, OleDb stuff, or DAO stuff. It would help if you could explain/define the following terms that I'm getting errors on:

DoCmd (some sort of command?)
acTable (assuming its an Access Table?)
acSaveNo
SysCmd (system command?)

What is acSysCmdGetObjectState???
There are also issues with RS! and UCase items.

I hope you can provide some assistance on sorting this out.  Thanks!
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hi mate, here's the finished code.
On the previous post I uploaded the entire solution, here's only the modified vb file.

The importsnt work is done inside btnCreateDB_Click
Here I copy the original mdb file, rename it, and delete all the records on the tblSurface that don't have the desires TimeID.
I do this for every distinct TimeID found.

Cheers,
Alex

Imports System.Data.OleDb
 
Public Class frmDBBrowser
 
    Private DBPath As String = Application.StartupPath() & "\DB\AvgProbMay2.mdb"
    Private NewDBPath As String = Application.StartupPath() & "\DB\New_AvgProbMay_TimeID{0}.mdb"
    Private MainConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";User Id=admin;Password=;"
    Private NewDBConnectionStringTemplate As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;"
    Private dtTimeIDs As DataTable
 
    Private Sub frmDBBrowser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim conn As New OleDbConnection(MainConnectionString)
        Dim comm As OleDbCommand = conn.CreateCommand()
        comm.CommandText = "SELECT DISTINCT TimeID FROM tblSurface"
        comm.CommandType = CommandType.Text
 
        dtTimeIDs = New DataTable()
        conn.Open()
        dtTimeIDs.Load(comm.ExecuteReader())
        conn.Close()
 
        Me.lbTimeIDs.DataSource = dtTimeIDs
        Me.lbTimeIDs.DisplayMember = "TimeID"
 
    End Sub
 
    Private Sub lbTimeIDs_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbTimeIDs.SelectedIndexChanged
        Dim conn As New OleDbConnection(MainConnectionString)
        Dim comm As OleDbCommand = conn.CreateCommand()
        comm.CommandText = "SELECT * FROM tblSurface WHERE TimeID = " & DirectCast(lbTimeIDs.SelectedItem, DataRowView).Row(0).ToString()
        comm.CommandType = CommandType.Text
 
        Dim dtRecords As New DataTable()
        conn.Open()
        dtRecords.Load(comm.ExecuteReader())
        conn.Close()
 
        Me.dgvSurfaceData.DataSource = dtRecords
 
    End Sub
 
    Private Sub btnCreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDB.Click
        Dim InsertQryStr As String = "INSERT INTO {0} SELECT * FROM tblSurface WHERE TimeID={1}"
 
        Dim thisDBPath As String
        Dim conn As New OleDbConnection
        Dim comm As OleDbCommand
 
        For i As Integer = 1 To Me.dtTimeIDs.Rows.Count
            thisDBPath = String.Format(NewDBPath, i.ToString())
            System.IO.File.Copy(DBPath, thisDBPath)
 
            conn.ConnectionString = String.Format(NewDBConnectionStringTemplate, thisDBPath)
            comm = conn.CreateCommand()
            comm.CommandText = "DELETE FROM tblSurface WHERE TimeID<>" + Me.dtTimeIDs.Rows(i)(0).ToString()
            comm.CommandType = CommandType.Text
            conn.Open()
            comm.ExecuteNonQuery()
            conn.Close()
        Next
    End Sub
 
End Class

Open in new window

0
 
jvs7829Author Commented:
Wow!!! That's awesome!  Just one more thing...how do I define "dgvSurfaceData"?  Its the only thing that I had an error on that I'm not sure what it is.  Thanks a ton!!!
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
dgvSurfaceData is a DataGridView control on the form.

Also on the form there's a ListBox named lbTimeIDs and a button named btnCreateDB.

btnCreateDB_Click is the click event handler of the button and lbTimeIDs_SelectedIndexChanged is the SelectedIndexChanged event handler of the ListBox.

When you select a TimeID on the ListBox the DataGridView shows the data on tblSurface with that TimeID.
The button starts the data split process.
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
BTW, I've posted the complete solution yesterday. It's with a txt extension due EE file restrictions. Rename it to a rar extension and unpack it.
There you have everything ready to run... just update the vb file with this latest version above.
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
How's it going?
Any problems?
0
 
jvs7829Author Commented:
I'm getting an error on the conn.Open() saying OleDbException was unhandled.  Any ideas?

0
 
jvs7829Author Commented:
I wasn't able to rename the file extension as a .rar. to use that.  I've got a form with a button and a list box, but I'm having an error with the conn.Open() part.  Thanks!
0
 
jvs7829Author Commented:
Nevermind the conn.Open() I was able to fix it by taking off the "Application.StartupPath()" from the declarations and just listed the file paths.

Now, I'm having problems with the Me.DgvSurfaceData.DataSource = dtRecords in the following:

        Dim dtRecords As New DataTable()
        conn.Open()
        dtRecords.Load(comm.ExecuteReader())
        conn.Close()

        Me.dgvSurfaceData.DataSource = dtRecords

I defined dgvSurfaceData using:  Private dgvSurfaceData As DataGridView

This might be wrong.

Any further assistance would be great!!!
0
 
jvs7829Author Commented:
Ok, I figured it out....thanks a ton!  I redefined the dgbSurfaceData variable as New DataGridView.  For some reason there's not a .mdb for TimeID = 1, they start at 2.  I'll see if I can figure out why, but still its awesome!!!  Thanks!
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hi mate,
sorry I didn't help you on those latest issues but I wasn't around 'till now.

Anyway, I'm glad I could help you.

Cheers!
Alex
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Btw,

Try to change the loop to:
For i As Integer = 0 To Me.dtTimeIDs.Rows.Count-1

This might fix the error not generating the file for TimeID = 1


Cheers,
Alex
0
 
jvs7829Author Commented:
Thanks!  It seems that just changing the i Integer value to 0 and not having Count -1 works.  

For i As Integer = 0 To Me.dtTimeIDs.Rows.Cout

Finally!!!!  
0
 
jvs7829Author Commented:
I hate to ask, but is there a way to limit the records used to the the first 48?  I thought the following might work, but it doesn't.

For i As Integer = 0 To Me.dtTimeIDs.Rows.Cout < 48

Any suggestions?
0
 
jvs7829Author Commented:
What if I want to have the user identify the file to use???  I tried the code below and ran into problems.  I set up TextBox1 where they could put in the file name (minus the .mdb)

   Dim FileLoc As String = "C:\GEMSS\Apps\Kentucky Lake\Output\"
    Dim Scenario As String = TextBox1.Text

    Private DBPath As String = FileLoc & Scenario & ".mdb"
    Private NewDBPath As String = FileLoc & Scenario & "_new_TIMEID{0}.mdb"

I wasn't sure what to do about Application.StartupPath...the form will be in ArcMap, but the databases are stored in "C:\GEMSS\Apps\".

Thanks!
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
I hate to ask, but is there a way to limit the records used to the the first 48?
The faster approach is to add a condition right after the For, something like:

IF i > 47 Then
     Exit For
End If



For the custom file name problem... what's exactly your problem?
0
 
jvs7829Author Commented:
That worked!  Thanks again!!!

The problem with the custom file name is that it won't always be the same file name to split up.  So, I wanted to set up the form to allow for input of the .mdb name and the file path for the folder where it is stored.  The idea was to have a FolderBrowserDialog (fileloc) to go to the folder where the mdb is stored, then have a text box for the name of the mdb (scenario).  They could be combined for the DBPath using DBPath As String = fileloc & scenario & ".mdb", but for some reason its not working when I set it up that way.  Also, using that, the NewDBPath could be defined as fileloc & scenario & "_new_TIMEID{0}.mdb".  Or at least that's what I thought.  I get an object unhandled error every time I try to get that to work.

The code below shows what I was talking about with the part I'd like to use commented out because it doesn't work.  I just specified the folder location as FilePath instead of using the FolderBrowser yet.  Maybe the problem is in the NewDBConnectionStringTemplate

Thanks!

Public Class frmDBBrowser
    'Private FileLoc As String = "C:\GEMSS\Apps\Kentucky Lake\Output\"
    'Private Scenario As String = TextBox1.Text
 
    'Private DBPath As String = FileLoc & Scenario & ".mdb"
    'Private NewDBPath As String = FileLoc & Scenario & "_new_TIMEID{0}.mdb"
    Private DBPath As String = "C:\GEMSS\Apps\Kentucky Lake\Output\avg_lowflow.mdb"
    Private NewDBPath As String = "C:\GEMSS\Apps\Kentucky Lake\Output\avg_lowflow_new_TimeID{0}.mdb"
    Private MainConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
    Private NewDBConnectionStringTemplate As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}"
    Private dtTimeIDs As DataTable
    Private dgvSurfaceData As New DataGridView

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.