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

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

b0lsc0ttIT ManagerCommented:

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.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
b0lsc0ttIT ManagerCommented:
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!
JamieAuthor Commented:
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.



JamieAuthor Commented:
Many thanks for all your help - all working now!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.