Creating DB, Adding Tables, Reading Data (Access & SQL2000)

[robplatt]

I have done a lot of research and this is what im doing so far (in vb .net 2005)

 

To create my table in access im using ADOX:

       Dim dbCatalog As ADOX.Catalog = New ADOX.Catalog
       Dim dbTable As ADOX.Table
       dbCatalog.Create(mdbConn)    'conn string referenced globally
            dbTable = New ADOX.Table
               With dbTable
                   .Name = "Customers"
                   .Columns.Append("GUID", ADOX.DataTypeEnum.adGUID, 36)
                   .Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "GUID")
                   .Columns.Append("Company", ADOX.DataTypeEnum.adVarWChar, 255)
               End With
               dbCatalog.Tables.Append(dbTable)

 

That works wonderfully.

 

I'm using OLEDB to read the data back:

           cmd = New OleDb.OleDbCommand("SELECT * FROM LastTableUpdate", oLocalConn)
           dr = cmd.ExecuteReader
           While dr.Read()
                    msgbox myreader(0)
           End While

 

That works great

 

I'm using regular sqlClient to connect to sql2000, not going to display the code as im not having any problem with sql 2000...

 

Is there an easier way then having to use adox and oledb and sqlclient.net ?

 

 

 

second, my program will have the ability to drop tables. before i pull data from a table i see if it exists using adox: (by the way this is a boolean function)

 Dim AccessConnection As New System.Data.OleDb.OleDbConnection()
       Dim SchemaTable As DataTable

       AccessConnection.ConnectionString() = mdbConn
       Try
           AccessConnection.Open()
           'Retrieve schema information about Table1.
           SchemaTable = AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                                 New Object() {Nothing, Nothing, strTable})

           If SchemaTable.Rows.Count <> 0 Then
               'MsgBox("Table " & SchemaTable.Rows(0)!TABLE_NAME.ToString & " Exists")
               AccessConnection.Close()
               Return True
           Else
               'MsgBox("Table does not exist")
               AccessConnection.Close()
               Return False
           End If
       Catch ex As Exception
           Return False
       End Try

 

That works well....

 

Heres the problem, when I go to open an Access table using oledb, i check to see if it exists using adox. if it doesnt exist, i fire my createdatabase() subroutine (again, it runs at startup if the .mdb file is missing) to rebuild whats missing from the database. it then checks to see if tableexists=false (for every table) before it attempts to build it. the problem lies right before that.

 

Remember my "dbCatalog.Create(mdbConn)" command that creates the mdb file? I can't use that again when i 'rerun' my createdatabase sub. so i use this

           If My.Computer.FileSystem.FileExists("datastore.mdb") = False Then
               dbCatalog.Create(mdbConn)
           Else
               dbCatalog.ActiveConnection = mdbConn
           End If

 

I get this error "Aruguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

 

so in adox i have to reference an existing db file and all my searches online say i can only do that by opening it with adodb (which i dont want ANOTHER reference.)

 

I need to be able to Create a DB (adox), Add tables(adox), add records(oledb), drop table(oledb), recreate table if necessary(adox).

 

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=1089

I thought I could go off this code, but it doesnt work as he wrote it so i cant adapt it.

Edited July 20, 2006 by robplatt

[PlausiblyDamp]

Access can be a pain to work with, it might be easier to just ship an already created but empty database with the executable (possibly as an embedded resource) to save having to recreate it through code and therefore remove the dependancies on adox and adodb.

 

Are you also having the same problem with SQL?

[robplatt]

No im getting data from a remote server to store it on their computer. ive already got it creating the db from scratch and populating it with remote data. i want the ability to drop a table and reacreate it on the fly... no i need the ability. there has to be a way without using adodb.

[robplatt]

Ok, a new method would be to create the table using oledb "CREATE TABLE".. but i would need help. I tried a simple command and got a syntax error:

           oLocalConn.Open()
               cmd = New OleDb.OleDbCommand("CREATE TABLE Customers(" & _
               "GUID Text, Company Text, Name Text, Phone Text, Cell Text, Fax Text, Email Text, ShipToAddress Text, BillToAddress Text" & _
               ")", oLocalConn)
               cmd.ExecuteNonQuery()

[IUnknown]

It didn't like GUID as a column name as it's a keyword. Use [GUID] instead.

[robplatt]

You know. I'm surprised nobody offered up using sql to make the tables. Its working wonderfully. Now if could somehow BUILD an empty database file i could get rid of adox completely!

 

that may be where i embed an empty database file into my resources, then instead of creating it, i would just save it to disk.

 

which would be the best route/fastest? if i stay with adox do i have to include interop.adox.dll thats chillin in my debug folder when i release my program?