• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 944
  • Last Modified:

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 asp.net).

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

  • 2
  • 2
1 Solution

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 http://www.vb123.com/toolshed/99/createtables.htm 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.

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!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now