Build new Access database from .NET application.

  • Thread starter Thread starter Rich Wallace
  • Start date Start date
R

Rich Wallace

Hi all,

I'm currently working on a new solution that entails extracting key data
from MS SQL Server (2000) and essentially export a dataset to an Access
database. I may have been overthinking this but here's what I'd like to do:

I have an XML representation of the MDB structure I need to build each time
called tblClosing.XSD

1. Once I obtain a valid set of data as approved from the user, the source
data will be in a .NET datagrid in an ASP.NET app.
2. When the user clicks a button to export the data, I want to build a new
MDB file off of the XSD structure.
3. After the structure is built, I want to export the data from the
datagrid into the MDB file and close it off.

Again, I'm probably over thinking this so there may a much easier way,
however, the XSD contains the exact structure of the MDB that I need to send
off.

HELP?!!?
TIA
-Rich
 
Since you're creating a new MDB file each time and you don't know it in
advance- I think you're in good shape.

What you're proposing will work -but the catch will be in creating the new
..mdb file. Once you have your new database and table created, you can
simply loop through the rows in the dataset's datatables and the just fire
an update for the values of each row, or alternately you can set
AcceptChangesDuringFill to false when you call the ASP.NET app's
dataadatper's .Fill method. This will cause the rowstate of each row to be
stated as .Added. Then you can just have another adapter pointing to the
access db, (the insert logic will be virtually identical to what you'd use
for the SQL Server if you were going to update the db too) and then call
update. If you are going to use XML (ie DataSet.ReadXML or
DataSet.WriteXML) make sure to use the Diffgram option so you preserve the
rowstate

As always, I like using DTS on SQL Server if I have it available but it may
be a good bit of work employing it depending on how you are determining
what's going to ship off to the Access db.

I think you're in good shape .

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
Hi Rich,

I never tried it, however in my idea this should be posible.
You can make an access file using ADO ext 2.7 and create the tables.

In the dataset columns is the information about those columns, however as
far as I know not one by one usable in the create so that should be convert
that first using select case or an if statement.

Than it should be in my opinion be easy to do what you want using the create
table and the command.executenonquery.

And after that a normal update of the dataset.

(However I think it is easier to write direct the create statements with the
proper columnnames and types, when you want that therefore I have a sample
in VBNet)

I hope this helps a little bit?

Cor
 
Thank you for the answers...

I was able to create the mdb file and, by using and XSD, I can create the
main table and structure. All I need now is the actual export process from
the data source (SQL) to the MDB file.
Here's what I have thus far...

--------
Private Sub ExportData(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnExport.Click

Dim oSave As New clsDataExport
oSave.CreateNewDatabase("<serverpath>\<Share>")

End Sub
--------
Public Sub CreateNewDatabase(ByVal DbFile As String)

Dim catNewDB As New ADOX.Catalog()
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="
& DbFile)
catNewDB.ActiveConnection.Close()

ImportDBStruct(DbFile)

End Sub
--------
Private Sub ImportDBStruct(ByVal sPath As String)

Dim oAccApp As New Access.Application()

Try
oAccApp.OpenCurrentDatabase(sPath)

oAccApp.ImportXML(ConfigurationSettings.AppSettings(eliantexport.Web.Global.
CfgStructXSD) AcImportXMLOption.acStructureOnly)
oAccApp.CloseCurrentDatabase()
Catch ex As Exception
EventLog.WriteEntry("Data Export", ex.Message)
End Try

End Sub
--------

I like using the ImportXML method since I have full control of the main
structure and I can alter the XSD if structure changes in any way rather
than having to manage mode code.

My next step is to extract certain records from a SQL Server database based
on one or more records selected by the user. Here's an idea of what my grid
looks like to the user:
ID Product OrderDate SubmitOrder
1 Binder 7/15/2004 Yes (checkbox)
2 Pens 7/15/2004 No (checkbox)

This may sound a bit confusing so bear with me...
The records in the datagrid is a very small subset of the actual data I need
to send out to the Access database. So I run the inital query to bring back
only the data above and allow the user to select which records they want to
submit. Once they have all of the records selected that they want submitted
and click on the 'Submit' button, I need to know what records in the
datagrid have a SubmitOrder value of 'Yes', and then take only those records
and extract the ID value and then retrieve and populate a new dataset with
the details of the data and then send those records to the Access database,
in this case, only the record for the 'Binder' product, or ID = 1.

I was thinking maybe a loop routine, but if it's cleaner and easier, maybe
somehow push the dataset to the Access file?
 
Hi Rich,

I do not know if you need it, however you can get the format of the dataset
column with
\\\
mydadata = ds.tables(0).columns.datatype.name
///
I hope this helps.

Cor
 
Thanks Cor, I have the actual database built and ready to accept data, I'm
just stuck as far as how to get the data I have in the dataset to the Access
DB.

Any ideas on how to do that? Am I stuck with doing a more complex routine
using a loop and INSERT method or is there a cleaner route?

-Rich
 
¤ Thanks Cor, I have the actual database built and ready to accept data, I'm
¤ just stuck as far as how to get the data I have in the dataset to the Access
¤ DB.
¤
¤ Any ideas on how to do that? Am I stuck with doing a more complex routine
¤ using a loop and INSERT method or is there a cleaner route?

I would take a look at the "Using .NET DataSet Class" example at the below link. It uses SQL Server
as the destination but you could just as easily use Access:

http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul

Are you sure that on this page is written how you can make from an XSD file
an new clean Access datatabase?

I could not find it however it is so much, maybe I missed it.

Cor
 
¤ Paul
¤
¤ Are you sure that on this page is written how you can make from an XSD file
¤ an new clean Access datatabase?
¤
¤ I could not find it however it is so much, maybe I missed it.

No, the technique assumes the database and table already exists. Of course we know that the database
and table can be created via DAO, ADOX or Access automation. He seemed indicate that he had already
created the database and all that he needed to do was populate the table with data.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul,

When you start in the top of the thread you can see that his goal was to
create the access database from new, slowly we got at a point where we had
everything however not the starting goal what was creating in a simple way
the access database from XSD and I was hoping you was providing that.

Cor

No, the technique assumes the database and table already exists. Of course we know that the database
and table can be created via DAO, ADOX or Access automation. He seemed indicate that he had already
created the database and all that he needed to do was populate the table
Click to expand...
with data.
 
¤ Paul,
¤
¤ When you start in the top of the thread you can see that his goal was to
¤ create the access database from new, slowly we got at a point where we had
¤ everything however not the starting goal what was creating in a simple way
¤ the access database from XSD and I was hoping you was providing that.
¤
¤ Cor

Like he said, he was able to create the database and table using the XSD. The data import/export
method could be accomplished a few different ways. The first is to use the example I provide which
implements XML. Another would be to programmatically link the SQL Server table to the Access
database and then use an INSERT (Append) query to import the data into the Access table directly.

Since he wants to export from a DataGrid the direct method would not be possible.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul,
Since he wants to export from a DataGrid the direct method would not be
Click to expand...
possible.

Why not, I never did it however this should be a one of the possible very
simple methods

mydataadapter.update(directcast(mydatagrid.datasource, dataset))

Cor
 
Thanks guys,

I ended up running a loop on my dataset and performed an INSERT using the
Access.Application.CurrentDB.Execute object and it runs fine. Since the
project is pretty much due befoer I can get fancy with it, this will work
for now, but I'm sure I'll loop around after post-implementation and rethink
it.

-Rich
 
Cor, I agree.

Nothing much on how to create an MS access DB from an XSD file. I have been
strugling with this for a couple of days now, and it seem MS Access cannot
handle the XSD files generated from Visual Studio. (tried import XSD in
Access 2003)

When you drop such an XSD file in InfoPath it fully understands it's
structure. But also frome her, no way to export to MS Access.

So please MS techies, please be a little more detailed here. Which route can
I use to create the tables and releations in MS Access?
 
¤ Cor, I agree.
¤
¤ Nothing much on how to create an MS access DB from an XSD file. I have been
¤ strugling with this for a couple of days now, and it seem MS Access cannot
¤ handle the XSD files generated from Visual Studio. (tried import XSD in
¤ Access 2003)
¤
¤ When you drop such an XSD file in InfoPath it fully understands it's
¤ structure. But also frome her, no way to export to MS Access.
¤
¤ So please MS techies, please be a little more detailed here. Which route can
¤ I use to create the tables and releations in MS Access?

See if the following helps:

Importing and Exporting XSD Data in Microsoft Office Access 2003
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ac2003_ta/html/odc_ac_xsd.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
OK, I knowthat the original question of this thread was something else, but
hey ... it is a discussion thread, so new question do pop up.

Can anyone from MS tell me why MS Access (2003) cannot import XSD files from
Visual Studio.NET (2003)

Thanks,

Martin
 
Back
Top