Opening Access and appending tables

[Jets5387]

How can you open and manipulate ACCESS with VBScript? I am familar with opening other objects such as Excel but Access does not seem to work.

Also, once you have created the Access object can you use SQL statements to manipulate and append the tables.

Any help would be appreciated either some examples or just point me in the direction of where I need to go

Thank you,
Jets5387

 

[Parke]

Hope this helps:

' Create an Access database

Dim cat 'As New ADOX.Catalog
Set cat = WScript.createobject("adox.Catalog&quot
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"
set cat = nothing
' ==============

' Append fields to an Access database

Const adInteger = 3
Const adVarWChar = 202
Const adVarChar = 200
Dim cat
Dim tbl
Set cat = WScript.CreateObject("adox.catalog&quot
Set tbl = wscript.createobject("adox.table&quot
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb;"
tbl.name="MyTable"
tbl.columns.append "fname",adVarChar,30
tbl.columns.append "lname",adVarChar,30
tbl.columns.append "colID",adInteger
cat.Tables.Append(tbl)
set cat = nothing
' ==========

' Connect to an Access database

Dim cn
Dim rst
Const adStateOpen = 1
Const adOpenForwardOnly = 0
Const adOpenStatic = 3
Const adLockReadOnly = 1

Set cn = CreateObject("ADODB.connection&quot

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\projects.mdb;"
set rst = createobject("ADODB.RecordSet&quot

SQLString = "Select project_name from ProjectInfo"

rst.open SQLString, cn, adOpenStatic, adLockReadOnly

If cn.State = adStateOpen Then
MsgBox "opened Access"
End If

rst.MoveFirst

s = "project names are: " & vbcrlf
msgbox s
Do Until rst.EOF
s = s & rst.fields("project_name&quot & vbcrlf
rst.moveNext
Loop

msgbox s
' ===========

To update records, I will usually use an ADO recordset.

Parke