Use ADO to create/insert into a table for the current DB

[cjkoontz]

I would be very greatful (how much, unspecified) for the code that would allow me to create a new table within the current Access MDB.

I see the code for creating tables for MDB's that are elsewhere. But what I want is to do this for the MDB that I am currently editing.

 

[cjkoontz]

Never mind.

I remember, now, the key is how you define the connection; i.e. --

Set cnnLocal = CurrentProject.Connection

 

[cmmrfrds]

'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security

Dim cg As New Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

tb.Name = "Test"
tb.Columns.Append "col1", adInteger
tb.Columns.Append "col2", adVarWChar, 50
cg.Tables.Append tb

 

[koukouroukou]

or use this one

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Source = "onlineagent"
.Open options:=adCmdTable
.AddNew
.Fields("name&quot = "yourvariable"
.Fields("surname&quot = "yourvariable"
.Fields("adress&quot = "yourvariable"
.Fields("nothing&quot = "yourvariable"
.update
End With
rst.close
Set rst = Nothing

 

[koukouroukou]

my code is to apend data in the table after the creation with the ADOX code of cmmrfrds

 

[Dan01]

cmmrfrds, could you please explain why I get a "Error 3420: Object is no longer valid." I'm using Access2000 with this code:
Sub FileHandlings()
On Error GoTo Errorhandler
Dim cg As New Catalog
Dim tbl As New ADOX.Table
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

tbl.Name = "ECLR200D"
tbl.Columns.Append "InputDate", adInteger
cg.Tables.Append tbl
cn.Close
Set cn = Nothing

Exit Sub
Errorhandler:
MsgBox Err & " " & Err.Description

End Sub
Thanks, Dan.

 

[cmmrfrds]

The example was wrong for this case.
Set cn = CurrentProject.Connection

should be
Set cg.ActiveConnection = CurrentProject.Connection