Last auto-generated replication-id

Hi,
In an MS Access table I have a one-field primary key, that is an auto-
increment field of type Replication-id. After inserting a single
record using SQL INSERT, I want to obtain the key of the new record. I
have done this before with auto-increment fields of type Numeric and
then I can use "SELECT @@IDENTITY AS fieldname". But this doesn't work
for replication id's. So how do I find my new record?

Peter.

0
Peter
5/4/2007 7:05:29 AM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1551 Views

Similar Articles

[PageSpeed] 5

Peter Laman <p.n.laman@scarlet.be> wrote in
news:1178262329.725006.29060@y5g2000hsa.googlegroups.com: 

> In an MS Access table I have a one-field primary key, that is an
> auto- increment field of type Replication-id. After inserting a
> single record using SQL INSERT, I want to obtain the key of the
> new record. I have done this before with auto-increment fields of
> type Numeric and then I can use "SELECT @@IDENTITY AS fieldname".
> But this doesn't work for replication id's. So how do I find my
> new record? 

That is not suppored for Jet databases. Your only reliable method to
get the value is the same as with a standard Jet Autonumber field,
which is to do the Add in a recordset and store the value. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
5/4/2007 11:12:46 PM
Mark J <MarkJ@discussions.microsoft.com> wrote in
news:DA47752E-CE32-4AB0-9914-76A9C25889EF@microsoft.com: 

> try Max(IDENTITY )

Er, what makes you think the values will be added sequentially? This
*is* a replicated environment, where sequential values wouldn't be
usable. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
5/4/2007 11:13:16 PM
Hi, Peter.

> So how do I find my new record?

GUID's can be difficult to work with, but if there's no way to get around 
using the GUID, then place a unique index on your candidate key that doesn't 
allow NULL's (if you haven't already).  When you insert a new record, you 
can find it again by querying for the values in the candidate key, since 
there's only one record in the table with these values.  When that record is 
found, check the column with the GUID for its value if you need to use it as 
a foreign key.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact 
info.


"Peter Laman" <p.n.laman@scarlet.be> wrote in message 
news:1178262329.725006.29060@y5g2000hsa.googlegroups.com...
> Hi,
> In an MS Access table I have a one-field primary key, that is an auto-
> increment field of type Replication-id. After inserting a single
> record using SQL INSERT, I want to obtain the key of the new record. I
> have done this before with auto-increment fields of type Numeric and
> then I can use "SELECT @@IDENTITY AS fieldname". But this doesn't work
> for replication id's. So how do I find my new record?
>
> Peter.
> 


0
69
5/4/2007 11:19:14 PM
On May 5, 1:19 am, "'69 Camaro" <ForwardZERO_SPAM.To.
69Cam...@Spameater.orgZERO_SPAM> wrote:
> Hi, Peter.
>
> > So how do I find my new record?
>
> GUID's can be difficult to work with, but if there's no way to get around
> using the GUID, then place a unique index on your candidate key that doesn't
> allow NULL's (if you haven't already).  When you insert a new record, you
> can find it again by querying for the values in the candidate key, since
> there's only one record in the table with these values.  When that record is
> found, check the column with the GUID for its value if you need to use it as
> a foreign key.
>
> HTH.
> Gunny
>
> Seehttp://www.QBuilt.comfor all your database needs.
> Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
> Blogs:www.DataDevilDog.BlogSpot.com,www.DatabaseTips.BlogSpot.comhttp://www.Access.QBuilt.com/html/expert_contributors2.htmlfor contact
> info.
>
> "Peter Laman" <p.n.la...@scarlet.be> wrote in message
>
> news:1178262329.725006.29060@y5g2000hsa.googlegroups.com...
>
> > Hi,
> > In an MS Access table I have a one-field primary key, that is an auto-
> > increment field of type Replication-id. After inserting a single
> > record using SQL INSERT, I want to obtain the key of the new record. I
> > have done this before with auto-increment fields of type Numeric and
> > then I can use "SELECT @@IDENTITY AS fieldname". But this doesn't work
> > for replication id's. So how do I find my new record?
>
> > Peter.

Well, finally I decided not to have the key auto-generated, but rather
call CoCreateGuid myself and assign its value. The result is the same
and it eliminates the problem.
Thank for all responses.

Peter

0
Peter
5/5/2007 12:10:52 PM
Good job!  I'm glad you found a reliable solution.  Thanks for sharing it 
with the newsgroups, so that others may benefit, too.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact 
info.


"Peter Laman" <p.n.laman@scarlet.be> wrote in message 
news:1178367052.159222.264770@w5g2000hsg.googlegroups.com...
> On May 5, 1:19 am, "'69 Camaro" <ForwardZERO_SPAM.To.
> 69Cam...@Spameater.orgZERO_SPAM> wrote:
>> Hi, Peter.
>>
>> > So how do I find my new record?
>>
>> GUID's can be difficult to work with, but if there's no way to get around
>> using the GUID, then place a unique index on your candidate key that 
>> doesn't
>> allow NULL's (if you haven't already).  When you insert a new record, you
>> can find it again by querying for the values in the candidate key, since
>> there's only one record in the table with these values.  When that record 
>> is
>> found, check the column with the GUID for its value if you need to use it 
>> as
>> a foreign key.
>>
>> HTH.
>> Gunny
>>
>> Seehttp://www.QBuilt.comfor all your database needs.
>> Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
>> Blogs:www.DataDevilDog.BlogSpot.com,www.DatabaseTips.BlogSpot.comhttp://www.Access.QBuilt.com/html/expert_contributors2.htmlfor 
>> contact
>> info.
>>
>> "Peter Laman" <p.n.la...@scarlet.be> wrote in message
>>
>> news:1178262329.725006.29060@y5g2000hsa.googlegroups.com...
>>
>> > Hi,
>> > In an MS Access table I have a one-field primary key, that is an auto-
>> > increment field of type Replication-id. After inserting a single
>> > record using SQL INSERT, I want to obtain the key of the new record. I
>> > have done this before with auto-increment fields of type Numeric and
>> > then I can use "SELECT @@IDENTITY AS fieldname". But this doesn't work
>> > for replication id's. So how do I find my new record?
>>
>> > Peter.
>
> Well, finally I decided not to have the key auto-generated, but rather
> call CoCreateGuid myself and assign its value. The result is the same
> and it eliminates the problem.
> Thank for all responses.
>
> Peter
> 


0
69
5/5/2007 7:44:02 PM
Reply:

Similar Artilces:

Find the last, 2nd last and 3rd last data in a column
I have data listed in several colums and need to create a formula to detail the last (most recent) 2nd last and 3rd last piece of data in the column. I have used the following formula to display the last but cant edit this or create another formula to get the 2nd and 3rd last pieces of data. =LOOKUP(2,1/(A:A<>0),A:A) Thanks in advance Jamie If there are no empty cells in the range: last cell: =INDEX(A:A,COUNTA(A:A)) penultimate: =INDEX(A:A,COUNTA(A:A)-1) semi-penultimate: =INDEX(A:A,COUNTA(A:A)-2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme r...

Multiple Chart replication
hi I have a worksheet that contains six small pie charts but am having great difficulty in obtaining the same chart size throughout. Formatting each chart individually is taking a long time and it obtaining an exact size replicacation is proving to be an uphill task. any pointers or tips would be gratefully appreciated! I wrote an article in Tech Trax e-zine about pie charts, and there's some useful macros near the end. One of them helps make the charts the same size. Here's the article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=390 - Jon ------- Jon P...

How can I display worksheet summary page ie title/size/last updat.
I'm running MSExcel 2003. I am often working with large workbooks with mutiple worksheets. It would be v helpful if I could see/sort and print off a summary that shows worksheet properties similar to Explorer eg: workbook :xxxx ---------------- worksheet last update size names 1/2/04 64k companies: 5/2/03 893k etc... any ideas? Jon I wrote this sometime ago and it works as a workbook_open event (Needs to be placed in the ThisWorkbook code module). It could be adapted to work otherwise though. It adds a sheet and lists all the built-in and c...

How long will my capital last
Hi If I had a capital sum of say �100,000 and I was taking an annua income of 5% and I was getting 3.5% interest annually - Can you help m demonstrate how long my capital last? Thank you Dust -- Message posted from http://www.ExcelForum.com Dusty, =3DNPER(3.5%,-5000,100000,0) If, by 5%, you mean 5% of the original investment (=A35.000 each year), = your money will last for approximately 35 years at 3.5% interest. See Excel Help for more info on NPER(). Regards, Anders Silven "Durhamr" <Durhamr.yajun@excelforum-nospam.com> skrev i meddelandet = news:Durhamr.yajun@excelf...

Multi-select listbox question
I have a multi-select listbox and wish to obtain data from the last clicked record in the listbox (you'll notice that if you make multiple selections then only the last-clicked record has a dotted line around it). What, if any, listbox property identifies this record as last-clicked? Thanks Dave -- Posted via a free Usenet account from http://www.teranews.com ...

Months auto-filter with year twist
Hello, I made a new toolbar with 12 buttons, each running a macro: Selection.autofilter Field:=12, Criteria1:="=??/04/????" Operator:=xlAnd for april, Selection.autofilter Field:=12, Criteria1:="=??/05/????" Operator:=xlAnd for may, etc. but this gives me this month of all years. i need a toggle button, that once pressed will put "2005", for example in this macros inswtead of the ????. and i will make one for 2006 a well. TI -- yadaa ----------------------------------------------------------------------- yadaaa's Profile: http://www.excelforum.com/me...

Auto fit wrapped and merged cells
Using Excel 2003 if you have a number of cells that are merged and wrapped how can you get the rows to auto fit height - I am sure this used to work on previous versions of excel but does not seem to now (similarly for columns). Is there a fix for this or an option I need to select or deselect? Hi Elmo, See this macro from Jim Rech: http://tinyurl.com/4c5l6 --- Regards, Norman "elmo2" <elmo2@discussions.microsoft.com> wrote in message news:8F22A71D-4E92-4AFF-B5DC-646926FE3AF1@microsoft.com... > Using Excel 2003 if you have a number of cells that are ...

Event iD 2601 2604 2501
These errors have been popping up every fifteen minutes. They started a couple of days and won't go away. This is Exchange 2007 standard and there is no clustering. Event Type: Warning Event Source: MSExchange ADAccess Event Category: General Event ID: 2601 Date: 2/26/2007 Time: 10:43:24 AM User: N/A Computer: MAIL01 Description: Process MSEXCHANGEADTOPOLOGY (PID=1980). When initializing a remote procedure call (RPC) to the Microsoft Exchange Active Directory Topology service, Exchange could not retrieve the SID for account <WKGUID=DC1301662F547445B9C490A52961F8FC,CN=Microso...

why is my last digit converted to zero?
I'm using Excel 2003 and when I enter a 16-digit number or cut and paste a 16-digit number, the last digit is converted to zero. This does not happen with 15 digits or fewer. Excel's numeric precision is 15 decimal digits. If you need to calculate with the number, you probably won't miss anything. If the number is a key, such as a creditcard number, add an apostroph (single quote, " `") before it; it will not show, but it will cause the number to be treated as text. Another way is to format the cell as text before entering the number -- Kind Regards, Niek Otten ...

Exchange Event ID 565
Hi, I've recently performed a swiing migration from sbs2000 to sbs2003 standard. Everything has been runnig fine except i have recently notice a large number of failed security events; on a daily basis there can be between 500-1000 approx of the same event. I have searched the web a fiar bit over the last week but anything i can find is on eventid.net but is slightly different errors. i have also noticed there have been other people asking about this error in this group but no fix as yet. Can anyone point me to any further resources? Any help appreciated. Object Server: Microsoft ...

Need help in searching last occurrence of string in text file
Hi , I am not good in file handling in excel macro..i need some help from u. I have one huge text file and i want to search last occurrence of string in that file and then cut the data after that searched line till end of file and paste it into a new text file. Any help appreciated. thanks in advance. How huge is "huge" (in Megs)? -- Rick (MVP - Excel) <sameer2211@gmail.com> wrote in message news:8cb08938-cf0e-4925-95bb-725f72df9f8d@25g2000prz.googlegroups.com... > Hi , > > I am not good in file handling in excel macro..i need some help from > u. > I hav...

Generate tasks for new hires
Hi everyone, I am using Outlook 2003. On our staffing team, we have 5 people who complete several tasks when we have a new person joining the firm. For example: New Manager John Doe, beginning 11/15/05 Tasks: Bob--- assign mentor for John Doe add John Doe to employee database Jennifer-- assign performance manager for John Doe etc... I'm sure there must be an Outlook tool/add-in that can help with this. Ideally, I'd like to be able to enter in the name of the new hire and start date, and have the tasks automatically generated and sent to each person. Hopefully the tool would inse...

Last logged on by
Hello! Using exchange 2003. When I look under Adminstrative groups -> Server -> "name" -> First storage group -> mailbox store -> mailboxes you can se all the mailboxes that you have and last logged on by. Under last logged on by I can se that one person (me) have logged on on to diffrent acconts, even if I havent don it. WHAT is this? Best regards Micke Micke: Did you use your account as the service account for anti-virus or spam? Also, IIRC, even if you view someone's Outlook Calendar, it would mark the mailbox as being logged on by you. Regards...

How can I generate a file dependent on linker output, which the linker then needs to generate the final dll using VS2005?
I need to process the map file that's generated by link on a C++ project, in order to generate the the .DEF file I really want to link with. Effectively, I want to make only some of the exports from my DLL available, and also I want to reference them by ordinal only. For those of you who have looked at how MFC works, will recognize this as being similar to the method in MFC80.def I can't really see any way to do this, as there doesn't seem to be any simple way to run the link command again with similar arguments to those generated automatically. However, I did come up wit...

First and Last Problem
For some reason First and Last are giving the wrong data in a Totals query. I assume that somehow the data is not ordered correctly in the main table, but it does show correctly in the datasheet view. For example if I group the sample database below by Name and Year, First of Location should be B, unfortunately it shows up in the query as A. Name Year Stint Location Ed 1999 1 B Ed 1999 2 A Ed 2000 1 A Desired Result Name Year Stint Location Ed 1999 1 B (But shows up as A) Ed 2000 1 A Any ideas from someone who has seen thi...

Strange Auto Change Going on
I have an issue - when ever I enter HSA (apprevation for Health Savings Account) into a cell, it seem to automatically change it to HAS. This occures in upper case or lowe case entries. Is there something strange goining or I have I inadvertently done someting so Excel things HSA needs to HAS???? Thank you Look under tools>autocorrect options, it believes you have a typo when typing has. Note that any change will change it for the whole office suite. You can also press space and then ctrl z to undo the "corrections" -- Regards, Peo Sjoblom "DataGuy" <Dat...

Replicating Code!!!
Hi there folks, I am currently creating a student achievement spreadsheet and I a using ticks in each cell to state if the student has passed the unit o not. I am using a simple VBA code to have any cell, within a specifie range, ticked upon selecting it. If the cell within the specifie range, already has a tick, the code will remove it. The trick to th code is the use of the letter "a" in a cell that has had it's fon formatted to marlett!! My code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target,...

Extract last name from Last, First
I know this has to be a common question, but I can't find the answer... Using VBA, I need to extract the last name from the format: Last, First For example: the cell contents extract to a variable the value of JONES, AMY JONES ADAMS, HARRY ADAMS Your help would really be appreciated! I don't know how you are using it, but this shows how to get it in a variable. Sub dk() Dim lName As String lName = Left(ActiveCell, InStr(ActiveCell, ",") - 1) MsgBox lName End Sub ...

Not Remember Last Login Account
Is there something to tell Dynamics GP 10 to not remember the last logged in account? Everytime I log into their system as SA to do some update they call and ask me what to put in the username field. If they have to type it each time surely they will remember.... ARRRRGGGGHHHH! ------=_NextPart_0001_3E1A8ED5 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Allen Have a look at my post http://blogs.msdn.com/developingfordynamicsgp/archive/2008/07/29/hybrid-clearing-the-last-user-on-a-terminal-server.aspx Otherwise wait for the next release of the Support Debugging Tool which ...

Public Folder Replication #22
Hi, Just migrated from Exch 2000 to 2003. If I want to set or remove Public Folder replication I have to do this for each and every public folder. Is there any way to automate this?. We have quite a few PF's and it's a real pain. Thanks! J. ...

Event ID 1023
Hi All, I keep getting the following error in my Exchange server event viewer approximately every minute : Event Type: Failure Audit Event Source: MSExchangeIS Private Event Category: Logons Event ID: 1023 Date: 2004/12/29 Time: 09:32:41 User: N/A Computer: JNBSOS10 Description: DOMAIN\Administrator was validated as /o=DEFAULT/ou=THEWORLD/cn=Configuration/cn=Servers/cn=SERVER/cn=Microsoft DSA but was unable to log on to /o=DEFAULT/ou=THEWORLD/cn=Configuration/cn=Servers/cn=SERVER/cn=Microsoft DSA. Does anyone know how to rectify this ? couldn't see anything in knowledge base....

Concatenate first:last! worksheet
I need to concatenate 150 rows in 50 different worksheets for a totals page, and may need to add additional worksheets in later versions. So, I need P7 in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all worksheets to be concatenated on P7 in the totals page, etc. I am wondering if it's possible to use something like first:last! to concatenate the worksheet range, making it possible to include additional sheets added in before the current last worksheet. I am using "&" for the concatenate function now as I have more than 30 sheets ...

Getting Shell Task ID from lost process
I am experiencing problems when using the Shell function in that periodically the returned task ID connection is lost. The external task/application launched via the Shell is still active, but the task ID becomes invalid. I seem to remember that somewhere in Access there used to be a way of scanning all active processes on the machine and interrogating their details in order to "grab" the appropriate process again. However, I cannot remember the technique or the add-in that does this. Can anyone remind me of the technique please? Thanks I am using Access 2003 on Win...

Auto Fill Options #6
Until today, the default of my auto fill options was copy cells; in addition, I never saw the auto fill box, ever. I've disabled the show paste and show insert options so the box doesn't appear but it defaults to fill series and adds a day to each date I drag and drop. Holding down the control key overrides the fill series default but I'd like to go back to copy cells as the default. Can it be done? I think it depends on what you have selected. If I put 1 in A1, select just A1 and drag that fill handle down, excel copies it. If I put 1 in A1 and 2 in A2, select A1:A2 a...

Goto Last Cell function
Hello Group, I have an application where I would like to create a function that goes to the last cell in a spreadsheet. I can get this to work if I manually use the <Goto...special...last cell> function, but when I put this command into a macro and increase the length of the spreadsheet, the macro will only go as far as the last cell when the macro was saved. Any suggestions? Thanks, Julius Here's a slick formula from Harlan that will return what's in the last cell in a column, =LOOKUP(2,1/(1-ISBLANK(A1:A10000)),A1:A10000) if you need the whole column use A65535 inste...