Create table in MS Access asp and update/insert only new records

Posted on 2008-11-13
Last Modified: 2012-08-14
Hi All,

1. How do I create a MS Access table using asp (not

2. Again using asp, when looping through a datafile - Update records that already exists and only insert new records when they do not already exist.

best regards

Question by:Jamie
    LVL 54

    Expert Comment


    It seems like you have 2 questions here.  Let's work on the first one in this question and you can open a new question for the other if you need it.  That one, especially if you want code or detailed help, deserves its own question.  Let us know what you decide on that.

    For creating a table in Access it is going to depend on how you can connect to the table.  What connection method are you using and what does your connection string look like (make sure you edit sensitive info before posting it here)?  As a start you could look at for a sample of the SQL query that *could* do this and some specific connection methods that won't support it.  Let me know if you have a question about what your connection string is.

    If this will work and you want help with the SQL statement to create the table then you will need to provide details on what fields it will have and their details.

    Let me know if you have any questions or need more information.

    LVL 2

    Accepted Solution

    Actually, it really doesn't depend on how you connect to the database if you do it correctly using ADO Extensions (ADOX) to programatically create a table using ASP.  Here's a sample of how to do it:

       Set cat = Server.CreateObject("ADOX.Catalog")
       Set tbl = Server.CreateObject("ADOX.Table")
       adVarWChar = 202
       adInteger = 3
       adDate = 7
       strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "db.mdb"
       cat.ActiveConnection = strConn
       With tbl
        Set .ParentCatalog = cat
        .Name = "MyTable"
        .Columns.Append "MyTableID", adVarWChar, 9
        .Columns("MyTableID").Properties("Jet OLEDB:Allow Zero Length") = False
        .Columns("MyTableID").Properties("Nullable") = False
        .Columns.Append "Field1", adInteger
        .Columns("Field1").Properties("Nullable") = True
        .Columns.Append "Field2", adDate
        .Columns("Field2").Properties("Nullable") = True
       End With
       cat.Tables.Append tbl
       Set tbl = Nothing
       Set cat = Nothing

    To answer your second question, you create a second connection to your database and use an IF statement.  If the value is found in your recordset, use an UPDATE statement through the second connection.  If the value is not found, use the INSERT statement.  If you don't want to use a second connection, copy the contents of your recordset into an array.

    LVL 54

    Expert Comment

    Good post and info.  I hadn't know about or forgotten about ADOX.  It seems like it will be exactly what the Asker needs and I am glad you posted it.  Thanks!

    Author Comment

    Hi WalkDog,

    Many thanks, I had no idea about ADOX, so a very useful solution - almost done...

    ... How would I delete the table before creating it using the ADOX code as above. I want to drop the table first, as I need to delete all the data before reloading a new set, otherwise the access database will grow and grow until I manually compact it? The access database is stored online.




    Author Closing Comment

    Many thanks for all your help - all working now!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    Making a simple AJAX shopping cart Couple years ago I made my first shopping cart, I used iframe and JavaScript, it was very good at that time, there were no sessions or AJAX, I used cookies on clients machine. Today we have more advanced techno…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now