HOWTO: data table into acess accdb using vb

  • Thread starter Thread starter GADOI
  • Start date Start date
G

GADOI

Hello:
Using VS2008 .net 3.5

I have a data table (MyDataTable) that has records i want to insert into an
access accdb table (AllAgents).
I am using the following function

Public Function OverWriteAgents(ByVal MydataTable As DataTable) As
String
Dim AccessConnection As New
System.Data.OleDb.OleDbConnection("PROVIDER=Microsoft.Ace.OLEDB.12.0;Persist
Security Info = False; Data Source=" + MdbPath)
Try
AccessConnection.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Try
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT
INTO AllAgents(LICNUM,NAME,EXPDATE) SELECT LICNUM,NAME,EXPDATE from
[MydataTable]", AccessConnection)
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
AccessConnection.Close()
End Try
End Function

but no records are updated because the inset statement contains the name of
the table but does not reference it.
I just dont know how to do this.
Help
Thanks
T
 
GADOI said:
Hello:
Using VS2008 .net 3.5

I have a data table (MyDataTable) that has records i want to insert into an
access accdb table (AllAgents).
I am using the following function

Public Function OverWriteAgents(ByVal MydataTable As DataTable) As
String
Dim AccessConnection As New
System.Data.OleDb.OleDbConnection("PROVIDER=Microsoft.Ace.OLEDB.12.0;Persist
Security Info = False; Data Source=" + MdbPath)
Try
AccessConnection.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Try
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT
INTO AllAgents(LICNUM,NAME,EXPDATE) SELECT LICNUM,NAME,EXPDATE from
[MydataTable]", AccessConnection)
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
AccessConnection.Close()
End Try
End Function

but no records are updated because the inset statement contains the name of
the table but does not reference it.
I just dont know how to do this.
Help
Thanks
T
Click to expand...

You are mixing two different worlds. One is your application, the other
one is the database engine. The database engine has no knowledge of your
application. It doesn't know what a DataTable is, and it has no knowledge about
a variable that you've once used to refer to an object a while back when
writing the application. All queries executed by the database engine are
based on tables and other queries.

In order to insert records, you must build the appropriate Insert SQL statement
for each record in a loop. You must use a different version of the Insert statement:

INSERT INTO table([list of fields]) VALUES ([list of values])

For this purpose, use the OleDbCommand's Parameters property. Within the loop
processing the DataRows in the DataTable, set each parameter's value to
the value from the corresponding field from the DataRow being processed.

You can also use an OleDBDataAdapter to synchronize the database with the
DataTable. With an OleDbCommandbuilder, the required Insert-, Delete- and
Update-SQL commands are created automatically, derived from the Select
command.

You'll find sufficient information in the ADO.net documentation:
http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx
 
Back
Top