how to add new table programatically

[Guest]

I am using VB .Net, although if you tell me how to do it in VBA that it is
just as well.

I have an existing Access database, and I want to add a new Table to that
database via a VB (or VBA) program. What References and commands do I use to
do this?

 

[Brendan Reynolds]

In VBA there are at least three possible ways of doing this - using DAO,
using ADOX, or using a DDL query. I would imagine the first two could be
done from .NET via com interop, but I have not tested this. The DDL method
should certainly work.

Public Sub CreateTables()

'DAO

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.CreateTableDef("FirstTable")
Set fld = tdf.CreateField("TestField", dbText, 50)
tdf.Fields.Append fld
db.TableDefs.Append tdf

'ADOX

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set col = New ADOX.Column
With col
.Name = "TestField"
.Type = adVarWChar
.DefinedSize = 50
End With
Set tbl = New ADOX.Table
With tbl
.Name = "SecondTable"
.Columns.Append col
End With
cat.Tables.Append tbl

'DDL

CurrentProject.Connection.Execute "CREATE TABLE ThirdTable (TestField
VARCHAR(50))"

End Sub

 

[onedaywhen]

In VBA there are at least three possible ways of doing this - using DAO,
using ADOX, or using a DDL query. I would imagine the first two could be
done from .NET via com interop, but I have not tested this. The DDL method
should certainly work.

For .NET, use System.Data.OleDb e.g.

using System.Data.OleDb;
....
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='C:\\MyJetDB.xls'";
OleDbConnection Conn = new OleDbConnection(strConn);
Conn.Open();
string strSQL = "CREATE TABLE MyTable (MyCol INT NOT NULL);";
OleDbCommand Comm = new OleDbCommand(strSQL, Conn);
Comm.ExecuteNonQuery();
Comm.Connection.Close();

Jamie.

--

 

[Guest]

The CREATE TABLE approach works great...thanks.

I was trying as well to use the DAO and ADOX approaches from VB .NET, and as
much as I tried, I could not figure out how to obtain an object reference to
an Access database that could be used with the DAO and ADOX commands. For
example, I tried:

Dim AccessApp As New Microsoft.Office.Interop.Access.Application
AccessApp.OpenCurrentDatabase("C:\Documents and Settings\...\filename.mdb")
Dim cat As ADOX.Catalog
cat.ActiveConnection = AccessApplic.CurrentProject.Connection

The last statement gives me a "Object reference not set to an instance of an
object" error. How do I obtain such a reference to an Access db object from
VB .NET?