Re: ADO conditional insert, update, or delete

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Paul Randall wrote:
"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:ef0A7ujQIHA.5524@xxxxxxxxxxxxxxxxxxxxxxx
Bob Barrows [MVP] wrote:
billy wrote:

I have a multi-dimensional array and an Access database. What I am
trying to do is to compare the array to the database:
If MyArray(0,0) does not exist in the database, then insert
MyArray(0,0) and MyArray(1,0) into table1.


Oh jeez , I feel kinda stupid now. I got so involved with all the
"fun" details that I failed to realize your goal was to make table1
contain the same data that is in your array. This should have been
so simple!! Forget the staging table!!! Two saved queries are all
that's needed:

ClearTable1 - deletes all the data in table1
InsertTable1Record - used in your loop to insert the data into table
1.

Again: see the Access group for help with constructing these saved
queries. The vbscript code to run these queries is the same as the
code I supplied in my initial reply


Hi, Bob

Assuming I've figured out the correct text for the queries, is it
possible for VBScript to save them to the access database without
running Access, and if so, can you provide sample code?


Yes, there are two ways:
- Use the JetSQL CREATE PROCEDURE statement
- Use ADOX to add a Procedure

I'm at work now so I don't have time to whip up some sample code, but
there are plenty of examples that could be found via Google. ISTR that
the JetSQL Reference section in Access online help had an example of the
first method.

I'll check back later on when I get home form work and see if you still
need me to compose something. Post a reply either way.

http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2003-36,GGLD:en&q=ADOX+Procedure
http://www.google.com/search?hl=en&c2coff=1&rls=GGLD%2CGGLD%3A2003-36%2CGGLD%3Aen&q=JET+CREATE+PROCEDURE


--
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.


.

Relevant Pages

  • Re: ADO conditional insert, update, or delete
    ... I have a multi-dimensional array and an Access database. ... MyArrayand MyArrayinto table1. ... Two saved queries are all ...
    (microsoft.public.scripting.vbscript)
  • Re: ADO conditional insert, update, or delete
    ... I have a multi-dimensional array and an Access database. ... Two saved queries are all ... I'm at work now so I don't have time to whip up some sample code, ...
    (microsoft.public.scripting.vbscript)
  • Re: Data Access Pages
    ... > queries, forms and macros to enable it to be operated over the internet. ... > data can be edited manually via data access forms, ... if there are any example of access database being controlled via web ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access and Excel
    ... via append queries and update queries. ... > Excel spreadsheets. ... > wanting to create some sort of Access database for her as ...
    (microsoft.public.access.tablesdbdesign)
  • Re: office 2003 Word mailmerge with Access
    ... If you aren't able to see all of the queries then ... it's likely due to the connection method rather than permissions. ... Select the Access database as the data source again and when ...
    (microsoft.public.office.misc)