DataSet to MDB file

[Guest]

I know that I can export a DataSet to XML. How can I save the DataSet to a
Microsoft Access MDB file?

Could you provide some sample code if it's a tough solution, please?

Thanks in advance!

 

[Cor Ligthert]

PJSimon,

You have to create your own table and columns using the SQL CREATE
statemenent and the command.executenonquery.

Than you can, when your rowstate are everywhere added, insert the dataset
using the insert command (eventually made by the commandbuilder) into your
access dataset.

I hope this helps,

Cor

 

[Doug Bell]

PJS,

You need to connect to the Access DB, then itterate through the DataSet and
append the records to the Access Table.
It can get a bit trickier if you need to check if the record has already
been appended and it needs to be updated.

I could not find the code for when I needed to do that but I used ADO to
delete old recoreds, update existing and append new.

SQL= "INSERT INTO tblMyTable (FieldA, FieldB) " & _
"VALUES (" & ValueA & ", " & ValueB") "

Doug

 

[Guest]

Thanks for the help, guys, but it's still not working I'm stuck in
establishing the connection to the database.

I'm using:

Private ConnectionString As String = "Server=local;" & _
"Database=SampleDB;" & _
"Integrated Security=SSPI;"
Dim scnnDatabase As New SqlConnection(ConnectionString)

Try
scnnDatabase.Open()
Catch ex As SqlException
MessageBox.Show(ex.ToString)
Exit Sub
End Try

... to establish my connection to the MDB file, but when I try to open the
connection, I get an error:

"SQL Server does not exist or access denied."
(I have set up an MDB connection in ODBC that points to the SampleDB.MDB
file.)

I have been researching this on the Internet and I'm starting to think I
need to download some plugin from Microsoft, but I dunno if that plugin
pertains to this issue. Can you help?

 

[Paul Clement]

¤ Thanks for the help, guys, but it's still not working I'm stuck in
¤ establishing the connection to the database.
¤
¤ I'm using:
¤
¤ Private ConnectionString As String = "Server=local;" & _
¤ "Database=SampleDB;" & _
¤ "Integrated Security=SSPI;"
¤ Dim scnnDatabase As New SqlConnection(ConnectionString)
¤

You need to use the OLEDB provider instead:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb"

Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
AccessConnection.Open()


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

That's the ticket! Thank you!