Can vbs append to Access table?

  • Thread starter Thread starter D.P. Roberts
  • Start date Start date
D

D.P. Roberts

I have a vbscript that appends data to a csv file, but I'd rather have it
append to a table in an Access database. Anyone know how to do this? Here's
the part of my code that appends to the csv file:

Set fsoOut = CreateObject("scripting.filesystemobject")
Set outFile = fsoOut.OpenTextFile("UserTrack.csv", ForAppending, True)
outFile.writeline (Now & "," & Computername & "," & Username)
outFile.close

Thanks in advance for any help with this...
 
D.P. Roberts said:
I have a vbscript that appends data to a csv file, but I'd rather
have it append to a table in an Access database. Anyone know how to
do this? Here's the part of my code that appends to the csv file:

Set fsoOut = CreateObject("scripting.filesystemobject")
Set outFile = fsoOut.OpenTextFile("UserTrack.csv", ForAppending, True)
outFile.writeline (Now & "," & Computername & "," & Username)
outFile.close
Click to expand...
Are you familiar with creating a database table in Access?
Are you familiar with creating a sql insert statement to insert values
into that table?
Are you familiar with using ADO to execute sql statements or saved
queries?

Where do we start?
 
I think I've got it working now. Thanks.

Bob Barrows said:
Are you familiar with creating a database table in Access?
Are you familiar with creating a sql insert statement to insert values
into that table?
Are you familiar with using ADO to execute sql statements or saved
queries?

Where do we start?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
No, but you were thinking all the right things. After posting the question,
I continued researching it on my own and found what I was looking for.

Thanks again...
 
Here's a sample VBScript that appends data from a text file (CSV by
default) to a table in a Jet database. So you could just create your
text file as now and then shove the data from that into the database.

'Sample VBScript to import data from a textfile into
'a table in a Jet (MDB) database without using Access

'Modify DB_NAME, TBL_NAME, DATA_SOURCE as required
'and the code that builds strSQL as necessary.

'If TBL_NAME exists, appends to it; otherwise creates it
'(crudely, using a make-table query).

'VBScript starts
Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String

Const DB_NAME = "C:\Temp\Test 2003.mdb"
Const TBL_NAME = "My_Table"
Const DATA_SOURCE = "[Text;HDR=Yes;Database=C:\Temp\;].B1#txt"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If
Next

If blTExists Then
strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"
Else
strSQL = "SELECT * INTO " & TBL_NAME _
& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL

oDB.Close
'VBScript ends


Alternatively, use code like that above to open the database and then
replace your
outfile.WriteLine
statement below with code that constructs an SQL string that will append
the current record to the table using the single-record syntax:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])
 
Back
Top