| Answer: |
*********************************************************
If you are looking for an answer for SQL Server,
check out this page on 4GuysFromRolla.
*********************************************************
|
*********************************************************
If you use the JET OLEDB version 4.0 (or later) driver to
connect to your database (that is, do not connect using the
Access driver), then you can use nearly the same technique with an
Access (".mdb") database as you can with SQL Server.
*********************************************************
|
Briefly, you do something like this:
...
Conn.Open "Provider=MICROSOFT.JET.OLEDB.4.0; " _
& "DATA SOURCE=" & Server.MapPath("somedir\somedb.mdb"
SQL = "INSERT INTO someTable (fld1, fld2) VALUES(123,'xyz');"
conn.Execute SQL ' do the insert, and then...
Set RS = Conn.Execute( "SELECT @@IDENTITY" )
idOfNewlyAddedRecord = RS(0)
RS.Close
...
|
If you wish to use SQL to add data to your Access DB, then this is undoubtedly the best way to do it. (And thanks to Xanderno for correcting my March 5th post.)
UPDATE: Torbjørn Laukvik reports that this technique works for him, even though he uses the Access driver instead of the JET OLE DB driver. He is using Win2000 and IIS 5. What can I say? Obviously, whether this works depends on the version of the driver you use. So try it with your current driver; if it works, fine. If not, use the recommended driver, instead.
However, if you want to use ADO-style coding to add records (in particular, you want to use ADODB.RecordSet.AddNew), then read on...
First, let me warn you away from some of the answers you might see.
Most of them are variations on this theme:
' *** BAD CODE ***
someConnection.Execute( "INSERT INTO table (fld1,fld2) VALUES(value1, value2)" )
RS = someConnection.Execute("SELECT Max(RecordID) FROM table")
newID = RS(0)
|
What is wrong with that, you ask? Suppose that, just after you execute the INSERT above, some other user comes along and also does just such an INSERT. The record you inserted got (say) a RecordID of 37. That user got RecordID 38. You get the Max(RecordID) and you get back...38!!
Oops.
Are there ways around this problem? Yes, but they involve locking the table (or, perhaps, entire database?) or the Application (via Application.Lock for a time that encompasses both the insert and the select. That works. It's not a horrible solution for the kind of lightly loaded site one would expect an Access-based Web site to be.
But perhaps the cleanest solution, especially when using Access, is the following:
(1) Be sure to have an Autonumber field in your table. Set to indexed (of course) and "no duplicates allowed". It may or may not be your Primary Key, your choice.
(2) Follow this code pattern:
RS.Open tableName, yourAlreadyOpenConnection, adOpenKeySet, adLockOptimistic, adCmdTable
RS.AddNew
RS("field1") = value1
RS("field2") = value2
...
RS("fieldN") = valueN
RS.Update
idOfAddedRecord = RS("nameOfAutonumberField")
|
That's all there is to it! It works. You may substitute adLockPessimistic for adLockOptimistic, as you wish. But the adOpenKeyset is critical.
Naturally, you must #include the adovbs.inc file in order to get those adXXX names defined.
For those who might be a little more curious, I present here an ASP page that you can run on your own system to demonstrate the truth of the above. To use it, you need an Access database (mine is named AddNewDemo.mdb) which contains a table (mine is also named AddNewDemo) that has the following fields:
(1) RecordID -- Autonumber
(2) UserName -- Text
(3) lockMode -- Text
(4) openMode -- Text
Put the ASP code here and the database into the same directory somewhere in your ASP virtual directories. And then invoke the page from your browser. Look at the output. Look at the source code. Put in a (dummy) name in the input field and push the button. See it all happen again. Note how only the two records added via adOpenKeyset behave the way we want. Enough said?
<HTML>
<HEAD>
<TITLE>Demo of using AddNew with Access</TITLE>
</HEAD>
<BODY>
<CENTER>
<H2>Demo of using AddNew with Access</H2>
</CENTER>
<%
' Constants, et al.
'
' Important values from "adovbs.inc"
'
' CursorTypeEnum
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
' LockTypeEnum
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
' CommandTypeEnum
Const adCmdText = 1
Const adCmdTable = 2
'
' Names for cursor and lock types:
'
OpenNames = Array( "ForwardOnly", "Keyset", "Dynamic", "Static" )
LockNames = Array( "??", "ReadOnly", "Pessimistic", "Optimistic" )
'
' Get value from last time on the page:
'
userName = Trim( " " & Request("UserName") )
If userName = "" Then userName = "*** demonstration startup ***"
' set up connection to the db...
'
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);" _
& "DBQ=" & Server.MapPath("AddnewDemo.mdb")
Set RS = Server.CreateObject("ADODB.RecordSet")
For openMode = 0 To 3
For lockMode = 2 To 3
RS.Open "AddNewDemo",Conn, openMode, lockMode, adCmdTable
RS.AddNew
preid = RS("RecordID") ' get ID *before* the update call...
RS("UserName") = userName & "-" & openMode & "-" & lockMode
RS("lockMode") = LockNames(lockMode)
RS("openMode") = OpenNames(openMode)
RS.Update
postid = RS("RecordID")
RS.Close
Response.Write "open mode " & OpenNames(openMode) _
& ", lock mode " & LockNames(lockMode) _
& ": before update id = [" & preid _
& "], after update id = [" & postid _
& "]<BR>" & vbNewLine
Next
Next
Set top10 = Conn.Execute( _
"SELECT TOP 10 * FROM AddNewDemo ORDER BY RecordID DESC" )
Response.Write "<P>Last 10 records added to DB:<UL>" & vbNewLine
Do Until top10.EOF
Response.Write "<LI>" & top10("RecordID") & ": " _
& top10("openMode") & ", " & top10("lockMode") _
& " [" & top10("userName") & "]" & vbNewLine
top10.MoveNext
Loop
Response.Write "</UL><P>" & vbNewLIne
top10.Close
conn.Close
%>
<H3>Please enter a name which we will construct a new record:</H3>
<P>
<FORM>
Name: <INPUT Name="UserName" Size=60><P>
<INPUT Type=Submit Value="Push to add to database">
</FORM>
</BODY>
</HTML>
|
*** ADDENDUM ***
Some of you have probably heard or read that using AddNew incurs both a performance penalty and a possible bug. I have yet to see hard evidence of the bug, but it is true that the nature of AddNew can cause a (relatively minor, but important on some sites) penalty.
Except...
Except that there is a little used and little discussed variant on AddNew that offers exactly the same performance and safety as INSERT. To wit: ADODB.RecordSet.AddNew arrayOfFieldNames, arrayOfFieldValues
So, you could alter the above code something like this:
<%
... this comes after the creation of the ADODB.RecordSet object ...
' the array of field names is the same throughout,
' so create it here, ahead of the loops...
Fields = Array("UserName","lockMode","openMode")
For openMode = 0 To 3
For lockMode = 2 To 3
' create the (dynamic) array of values...
Values = Array( userName & "-" & openMode & "-" & lockMode, _
LockNames(lockMode), OpenNames(openMode) )
RS.Open "AddNewDemo",Conn, openMode, lockMode, adCmdTable
' now use the "better" form of AddNew...
RS.AddNew Fields, Values
postid = RS("RecordID")
RS.Close
Response.Write "open mode " & OpenNames(openMode) & ", lock mode " _
& LockNames(lockMode) & ": after update id = [" _
& postid & "]<BR>" & vbNewLine
Next
Next
...
%>
|
It works well! Give it a try!
|
