VBScript Automation of Data Entry to an Access Database

  • Thread starter Thread starter Steve Munson
  • Start date Start date
S

Steve Munson

I have learned a lot by looking at the object models
within the VBA help for Excel and Outlook, but for
Access, I can't seem to locate any methods for some of
the most basic functions of Access: things like adding
data to a table or being able to list the fields and
their sizes, and what I am finding all seems to be read
only. I have Windows XP and Office 2002 with the FULL
VERSION of Access, and I want to use a VBScript to
instantiate Access, then add data to a table. I've had
no trouble putting data in an Excel spreadsheet, but I
just don't seem to be getting anywhere with Access. All
I've managed so far is to instantiate Access, open an
existing database, and list the tables. Can someone
provide details or an explanation of how this is done in
VBSript? Thanks in advance for any/all assistance.
 
and I want to use a VBScript to
instantiate Access, then add data to a table.
Click to expand...

This is an extract from a vbs script I use to interpret SQL CREATE DATABASE
scripts... you should get the idea

Option Explicit

Const c_dbOpenSnapshot = 4
Const c_dbForwardOnly = 256
Const c_dbFailOnError = 128

Dim dbe, db
Dim strSQL
Dim rs
Dim tsInFile


' this is the crunch line: check your registry for the
' class name, although it is probably the same
'
Set dbe = CreateObject("DAO.DBEngine.36")

' This creates an Access 2000 (i.e. Jet 4) database
'
Set db = dbe.CreateDatabase(strMDBFile, _
";LANGID=0x0409;CP=1252;COUNTRY=0", _
64)

' here excluded loads of code to get a SQL command
' from a script file...

If left(strSQL, 6) = "SELECT" then
Set rs = db.OpenRecordset( _
strSQL, c_dbOpenSnapshot, c_dbForwardOnly)
WScript.Echo "Result = " & rs.Fields(0).Value

Else
db.Execute strSQL, c_dbFailOnError

End If


db.Close

and this is what the script looks like:

create table Patients
( PINumber counter constraint PK primary key,
FName text(20),
LName text(20)
);

insert into Patients (FName, LName) Values ("James", "Mason");
insert into Patients (FName, LName) Values ("Alicia", "Silverstone");
insert into Patients (FName, LName) Values ("Marissa", "Tomei");


Hope that helps


Tim F
 
1st ... THANKS!
2nd ... RUT-ROW REORGE!

It appears that what you do is execute an SQL query that
results in adding the data, and you interpret a separate
script to be able to form that query. My problem now is
that I'm just not familiar enough with SQL to have any
idea how it works on the input side of the house, and I
would probably struggle long past the cows coming home if
I had to join just 2 tables to get an output query going.

I've been isolated from the details of that stuff by
virtue of having tools like MS Access hanging around.
I'm willing to spend a phone call on this if that makes
any sense. I think I have the concept down, but as
always, the devil is in the details....

Let me know if you can help further. Thanks again!

Steve
 
2nd ... RUT-ROW REORGE!

It appears that what you do is execute an SQL query that
results in adding the data, and you interpret a separate
script to be able to form that query.
Click to expand...

Yup: far and away the easiest way to do any kind of database manipulation:
it's what SQL was created for...
My problem now is
that I'm just not familiar enough with SQL to have any
idea how it works on the input side of the house, and I
would probably struggle long past the cows coming home if
I had to join just 2 tables to get an output query going.
Click to expand...

Well: INSERTing data into table is very easy: you can check out the help
file on the command, and you can see the example I posted. It's much
quicker and simpler than reams of rs.AddNew / rs!MyField="Eric" /
rs!OtherField=23 / rs.Update and so on.
I've been isolated from the details of that stuff by
virtue of having tools like MS Access hanging around.
Click to expand...

Quickest way to get a complex SQL query written is to do in in the Access
query grid, test it and debug it, then switch to SQL View and simply copy-
and-paste wherever you want it to go.


By the way, if you can write VBA inside Access, you can write VBS in a
script. You lose the richness of proper data typing and so on, but the
biggest difference is that you have to create your own DBEngine and
CurrentDB() etc., and they were in the example I posted too.

Hope that helps


Tim F
 
Tim,

Thanks so very much for providing that little extra
detail. I had to guess that there would be
an "OpenDatabase" method, and sure enough, it even
took my string argument for a particular filename of the
database I wanted to open.

I've now been able to get things working, and while
it's taken a bit of trial and error, I've even managed to
figure out how to get past my validation rules.

I'm now wondering if there's any documentation anywhere
that describes all the methods of this class, it's parms
and such? I would imagine that seeing a larger sample
of code that uses this technique would be good exposure
for me now. I would like to prepare some error trapping
and the documentation would be quite useful in that
regard.

Finally, that post of mine that had the 2nd line that
said "RUT-ROH REORGE" was a reference to an old kids
television show called "The Jetsons" that aired back in
the 1960's. The lead character had a dog that could
sort of talk, and that statement was what the dog would
say every time he got the lead character in trouble as a
result of said lead character trying to get the dog to
behave. You kinda would have to have seen it to get it.

Thanks again,

Steve
 
I've even managed to
figure out how to get past my validation rules.
Click to expand...

--? You're not meant to "get past" validation rules: they are there to
protect the data.
I'm now wondering if there's any documentation anywhere
that describes all the methods of this class, it's parms
and such?
Click to expand...

It is _exactly_ the same object model as you are used to in Access, so you
can refer to the help files, or the Object Browser, or any decent book that
references DAO programming. If you should prefer to use ADO then it also
follows the very same rules -- for example you start with something like
CreateObject("ADODB.Connection") and go from there.

The (one) neat thing about COM objects is that they look the same from any
programming environment, be it VBA/Access, VBS, Delphi, VB.Net etc etc.
including C++. If you can program within Excel, then you can use the same
code to automate it from within CorelDraw!
Finally, that post of mine that had the 2nd line that
said "RUT-ROH REORGE" was a reference to an old kids
television show called "The Jetsons" that aired back in
the 1960's.
Click to expand...

Little matter of the Big Pond... <g>

All the best


Tim F
 
Pardon my junk e-mail address, but I seem to all of a
sudden be getting deluged with junk, some of which
appears to be coming from Microsoft. Anyway, my
validation rules were stopping me from adding data
because I didn't quite fully understand how to properly
enter them, rather than my having bad data.

It's good to know the object model is the same, but given
the problems I had identifying anything useful there, as
opposed to my good experiences with the Outlook, Excel,
and Word object models, I may have to come back here for
more assistance.

And lastly, as to the "little matter" of the "Big Pond",
just be aware that on this side of what you're referring
to, that term more accurately describes the Pacific Ocean.
 
opposed to my good experiences with the Outlook, Excel,
and Word object models, I may have to come back here for
more assistance.
Click to expand...

That's okay: it's what we are here for! It's easiest to learn and test DAO
programming within Access/ VBA and then just port the code _nearly_
verbatim into VBS etc.

All the best


Tim F
 
Back
Top