Re: Build new Access database from .NET application.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Rich Wallace (rich.wallace_at_minusthecannedmeat.jfsheadotcom)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 12:52:15 -0700

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?

"Cor Ligthert" <notfirstname@planet.nl> wrote in message
news:%23hWiXCIdEHA.3016@tk2msftngp13.phx.gbl...
> 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
>
>

Relevant Pages

  • Thank U !! - Re: Access looks for a particular drive.
    ... The suggestions you offered were of great and instant help. ... > locate the .mdb file in the first place. ... >>A colleague was programming our Access database before I was asked to ... >> few changes on it because we changed PCs. ...
    (microsoft.public.access.forms)
  • Re: Problem accessing Access/Jet databse: Could not lock file
    ... not lock file" when opening a connection to an Access database on a network ... shared folder. ... Since the .mdb file can be opened using Access and everything can be done, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How do I import a database from another computer?
    ... What format does the original have to be saved in so it can ... An Access Database is a complex object, ... container for multiple forms, reports, modules, queries, tables, and ... .mdb file as it can potentially contain damaging code). ...
    (microsoft.public.access.gettingstarted)
  • Re: Caching data
    ... My problem is a relatively tight deadline so I can't do too much search forehand... ... I ended up using ADO and created an Access database on the fly. ... My application just deletes the mdb file on completion. ... I used the standard ADO classes with Delphi, and an ADOX call to create the mdb in the first place. ...
    (borland.public.delphi.non-technical)
  • Re: Simple Database Connection Code
    ... MyDataBase = your Access .MDB file, give a full path of your file location, ... MyTable = your table in Access database. ... > Does anyone have some basic code and the corresponding resources needed ... > blah, blah, blah. ...
    (microsoft.public.dotnet.languages.csharp)