Can I create an Access table from VB? 2

[wusinik]

I'm looking to allow users to create a custom table by editing an existing table or creating a new table from scratch. Can VB allow for a createTable - like property?

Thanks

 

[s2001]

Hi

yes u can defintely create Access database and tables through VB. Use DAO model to do that.

This code will do that:

Private wrkDefault As Workspace
Private dbsNew As Database
Private tdfNew As New TableDef

Set wrkDefault = DBEngine.Workspaces(0)
Set dbsNew = wrkDefault.CreateDatabase("MDB.mdb", _
dbLangGeneral, dbEncrypt)
Set dbsNew = wrkDefault.OpenDatabase("MDB.mdb", True, False) 'open the created Database
Set tdfNew = dbsNew.CreateTableDef("TableName&quot
with tdfNew
.Fields.Append .CreateField("Feild1&quot 'Create a new field
end with
dbsNew.TableDefs.Append tdfNew 'add the table to the DB

dbsNew.Close 'close db

The above will create a database, table and field.
Hope this works.
Let me know if u need more information.

Thanks

PS: Make sure to have a referance MS DAO 3.6 Library. Murali Bala

 

[AdaHacker]

Yes, you can build databases is VB. Here's an article that goes into some depth on how to do it with DAO.

http://pc13.virtualave.net/dbtutorial.htm

 

[wusinik]

I have implemented the code but I get a run time error when the code hits the line:
dbsNew.TableDefs.Append tdfNew 'add the table to the DB
the eroor is :
Run time error '3259':
Invalid field data type

Any ideas why this is occuring? The only real change I made to this code is I referenced a pre-existing database in the line:
Set dbsNew = wrkDefault.CreateDatabase("MDB.mdb", _
dbLangGeneral, dbEncrypt)...I don't need to create a database, I just need to add a table to it.

Thanks

 

[petermeachem]

Look up CREATE TABLE sql. Peter Meachem
peter@accuflight.com

 

[s2001]

Hi

Put this in the code:

with tdfNew
.Fields.Append .CreateField("Feild1", dbtext)
end with



Murali Bala

 

[wusinik]

That dbText did the trick - thanks so much!

 

[wusinik]

OK, now here's the last step, I want to fill this new table with the contents of a dbgrid...I have the contents of the dbgrid saved into a table named template. I want to save the contents of template/dbgrid as NEW_Table and then clear the contents of template so that I can repeat this process.

 

[s2001]

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strcnn As String
dim sValue as string

cnn.ConnectionString = "ODBC;DBQ="MDBFile.mdb" & ";UID=;PWD=;Driver={Microsoft Access Driver (*.mdb)}"

cnn.open
Set rs = New ADODB.Recordset
set rsNew=New ADODB.Recordset

strcnn = "SELECT * FROM Template"
rs.open strcnn, cnn, adOpenDynamic, adLockOptimistic
sValue=rs.Field("FieldName&quot.value
rs.close

strcnn="SELECT * FROM NEW_Table"
rsNew.open strcnn, cnn, adOpenDynamic, adLockOptimistic
rsNew.addNew
rsNew.fields("FieldName&quot =sValue
rsNew.close

cmd.CommandText = "DELETE * FROM Template"
cmd.CommandType = adCmdText
cmd.CommandTimeout = 15
cmd.Execute

set rs=Nothing
set rsNew=Nothing

If you have many records in Template table you can iterate through them using Do while Not EOF..and then writing it to the New table. Hope this Helps..

Thanks Murali Bala

 

[wusinik]

I get an error -

Compile error:
Expected: end of statement

on the line -

cnn.ConnectionString = "ODBC;DBQ="MDBFile.mdb" & ";UID=;PWD=;Driver={Microsoft Access Driver (*.mdb)}"

Then the cursor highlights the quotation mark where MDBFile.mdb is referenced. Do I need to do something like
&"""&... ?

 

[s2001]

dim MDBPath as string
MDBPath="c:\MDBFile.mdb"

cnn.ConnectionString = "ODBC;DBQ=" & MDBPath & ";UID = Admin ;PWD=;Driver={Microsoft Access Driver (*.mdb)}"

This should work...
Murali Bala