Export a datatable into an access DB

[Wim]

Dear all,

First of all I want to say that I am not a programmer at all. From time to time a have used VB6.0. At the moment I am writing a program in VB.net which basicly reads some data calculates lot of numbers and gives an output. This last part gives me troubles.
I have constructed a datatable with the code. Now I want to put this table into access using the following code:

 Public Sub writeoutputtable()
        Dim sSQL As String

        open_connection(database)

        sSQL = "Select dtOutputtable.* INTO " & Outputtablename & " FROM dtOutputtable"
        cmdupdate = New OleDbCommand(sSQL, cndatabase)
        cmdupdate.ExecuteNonQuery()

        close_connection(database)

    End Sub

The open_connection and close_connection are other procedures with following code:

 Public Sub open_connection(ByVal database As String)
        Dim sconnexion As String

        sconnexion = "Provider=Microsoft.Jet.OLEDB.4.0;"
        sconnexion &= "Password=;User ID=Admin;"
        sconnexion &= "Data Source=" & database

        cndatabase = New OleDbConnection(sconnexion)

        Try
            cndatabase.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Public Sub close_connection(ByVal database As String)
        Try
            cndatabase.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

dtOutputtable is declared as a datatable

The program stops at the ExecuteNonQuery statement with the following message:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

What do I wrong?

 

[vis781]

Just so i'm up to speed here. You have a created a DataTable in code and now you wish to put that table into an Access DataBase, but the table does not currently exist in the Access DB?

 

[Wim]

That is Correct

 

[vis781]

In that case i'm sorry to say that it has just got quite a bit more complicated for you... I'm afraid to say that you can't just add a table created in code to an Access database with an Update query. You Have to use DAO to create the table in Access first.

 

[cjard]

In that case i'm sorry to say that it has just got quite a bit more complicated for you... I'm afraid to say that you can't just add a table created in code to an Access database with an Update query. You Have to use DAO to create the table in Access first.

Jet DDL can create a table via normal SQL:

http://support.microsoft.com/kb/180841

The psuedocode for wims app would look like:

  Dim conn as New OleDbConnection("connetion string goes here")  
  conn.Open()
  
  Dim cmd as New OleDbCommand("CREATE TABLE whatever(colName TEXT(50))", conn)
  cmd.ExecuteNonQuery() 'create the table


  'now insert the data one row at a time. build the command
  cmd.CommandText = "INSERT INTO whatever(colName) VALUES(?)"
  cmd.Parameters.Add("colName", OleDbType.VarChar)

  For Each ro as DataRow in whateverDataTable.Rows
    'now repeatedly change the value to be inserted and re-call the SQL over and over
    cmd.Parameters("colName").Value = ro.whateverValue
    cmd.ExecuteNonQuery()
  Next ro

.Net 1.1 data access is so labour intensive..

 

[vis781]

Quite right, but DDL is limited compared to DAO in that it can't do wonderful things like cascade updates, or deletes.

 

[cjard]

Agreed - horses for courses though; the requirement here of simply making a table is covered by DDL, and means objects can be reused without importing extra libraries

Wim: ADOX is another technology that lets you perform a wide variety of ops on an MDB

 

[Wim]

Thanks guys,

I will try one of the options. I suppose it is also possible to create the table on beforehand in access (my output is a standard table with the same design) and complete it by the code. The question then would be if we can save the completed table under another name so that the empty table still exists?

 

[cjard]

Wy would you do that?

 

[Wim]

The meaning of the program is that I can do different runs with, for example, different parameters. The ouputtables should not be overwritten.

 

[cjard]

OK, so

Make an MDB in access, with one table, that is empty
Call it ORIGINAL.MDB
COPY this to a new file called OUTPUT.MDB
Follow the DW1 link and learn how you attach to OUTPUT.MDB and write data into that table
Now, in code when making a new set of outputs:
Copy ORIGINAL to OUTPUT, in code with System.IO.File.Copy. Overwrite OUTPUT.MDB if you want
Use your code to fill the table.
Copy the OUTPUT.MDB to wherever you want


ORIGINAL will always be a clean, blank, empty database
Never write into it!

 

[Wim]

Thank you for all your help. This morning I succeeded to create a table and write in the table with DDL (only a test DB with one colom but to extend the code should not be a problem).
So my problem is solved. Thanks again for your clear explications to a non developper.