Hi Jeff,
The commas and quotes in this look badly screwed up:
app.DoCmd.RunSQL(UPDATE )'tbl_1 SET tbl_1.Acct = Replace([Acct],’ ‘,’’),
tbl_1.Name = Replace([Name],’ ‘,’’), tbl_1.TDate = Replace([TDate],’ ‘,’’),
tbl_1.TResult = Replace([TResult],’ ‘,’’), tbl_1.TTime = Replace([TTime],’
‘,’’), tbl_1.Phys = Replace([Phys],’ ‘,’’))
It's much more readable if you use the VB line continuation symbol
(space+underscore) to break it up:
app.DoCmd.RunSQL("UPDATE tbl_1 SET " _
& "Acct = Replace([Acct], ' ', ''), " _
& "[Name] = Replace([Name], ' ', ''), " _
...
& "Phys = Replace([Phys], ' ', '');"
One possible problem is that the Replace() function is not available in
queries in older versions of Access.
A wrinkle I often use when writing anything but the simplest VBScript is
to do it in a module (with Option Explicit at the beginning!)in an
Office application other than the one I'm planning to automate with the
script. So for this one I might use Excel. I'd start by setting a
reference to the library/ies I needed (in this case Access.Application
and perhaps Microsoft Scripting Runtime, which contains the core
VBScript objects) and then declare the variables with explicit types
(which you can't do in VBScript). E.g.
Dim App As Access.Application
Doing it this way means I take advantage of the shared syntax and
functions, and get the benefit of Intellisense as I write and edit the
code. The VBA compiler warns me of many syntax errors. And it's easy to
set breakpoints and step through troublesome code.
Once the code is running happily in this form I change all the variable
declarations to be untyped - leaving the original type declaration as a
reminder
Dim App 'As Access.Application
and test the code again. Next I remove the references to the libraries:
this will reveal problems with undeclared constants such as
acimportFixed. When the code works in that form, I paste it into a new
text file and try running that as a VBScript.
Here's something simpler than DAO_Execute.vbs:
'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening 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.
'Works as is with a CSV file. For other delimited files
'or fixed-width files, the file specification must be supplied
'in a schema.ini file in the same folder.
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"
'Textfile is C:\Temp\B1.txt
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
Thanks for your reply John:
I have been looking at the material you recommended yesterday too, honestly
you are really REALLY far ahead of me on most of the material but I am
working on it. I could not for the life of me figure out how to use your
DAOExecute.VBS script.
The problem SQL I am having is with an update query which is removing spaces
and another update query that inserts a colon. I have cut and pasted the SQL
from Access and manipulated it a number of times.
The database I built with all the queries works, basically importing data
and then cleaning it and moving it reformatting text into proper date and
time fields. Since it is based on reports coming from outside access and I
was able to automate the import/append of the raw data, I thought I would
just try and automate the action queries too.
The following is the .VBS file I have and it works with the exception of the
second query which is set apart with a space before and after.
const acimportFixed = 1
const acQuitSaveNone = 2
set app = createobject("Access.Application")
app.OpenCurrentDatabase("U:\jeff.mdb")
app.DoCmd.TransferText 1, "2Import","tbl_1","S:\Text Files\1.txt"
app.DoCmd.TransferText 1, "2Import","tbl_2","S:\Text Files\2.txt"
app.DoCmd.TransferText 1, "3Import","tbl_3","S:\Text Files\3.txt"
app.DoCmd.TransferText 1, "4Import","tbl_4","S:\Text Files\4.txt"
app.DoCmd.DeleteObject acTable, "1_ImportErrors"
app.DoCmd.DeleteObject acTable, "2_ImportErrors"
app.DoCmd.DeleteObject acTable, "3_ImportErrors"
app.DoCmd.DeleteObject acTable, "4_ImportErrors"
app.DoCmd.RunSQL "DELETE tbl_1.TResult FROM tbl_1 WHERE (((tbl_1.TResult) Is
Null))"
MsgBox "Data Import Sequence Complete.", , "Calculator"
app.quit 2
set app = nothing
TDate is a date field, TTime is a number field, all the others are text
fields.
You can see I have four tables, each table has 6 update queries that are run
which I have sequenced in VB behind a command button.
I just thought this might be a good exercise to try and figure out how to
script by running all 20 some queries in a VBScript.
