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.

Im 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

Im 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 cant 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.

 

[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 didnt like GUID as a column name as its a keyword. Use [GUID] instead.

 

[robplatt]

You know. Im 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?