Solved

Replication ID in New Records

Posted on 2013-06-03
6
754 Views
Last Modified: 2013-06-03
Hi Experts,

I'm currently maintaining a system I built which is connected to an SQL database. The SQL database has two front end applications, 1) The native app that was built for this database and 2) my app, which extends the functionality of the original system.

It's a payroll system. And I've been asked to split one of the payments entered into multiple checks. The problem is that the table that I need to work with uses a field of data type NUMBER and field size REPLICATION ID. I need to split a single record into multiple records with different REPLICATION IDs, but I don't know how to generate a REPLICATION ID.

If this were an AutoNumber field, it would be easy to accomplish, but it's not.

ANY SUGGESTIONS?

Any help would be greatly appreciated.
0
Comment
Question by:eantar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 39216773
for the rep id, isn't it just a GUID? If so, use the NewID() function
http://www.dailycoding.com/Posts/generate_new_guid_uniqueidentifier_in_sql_server.aspx
0
 
LVL 5

Author Comment

by:eantar
ID: 39216834
Thanks for your reply, but I'm trying to find out if there is an Access solution for adding a new GUID, something that works the same as NEWID(). If I have to use a pass-through query, do you know what the syntax of that query would be?

Thanks,
E
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39216916
If you create the records in your extending app, all you need is to set the table to have an AutoNumber field and for that set the field size to Replication Id. Then any new record will be assigned a GUID as the autonumber.

/gustav
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39216934
If you have to create a GUID string in code, here's a method:
Option Compare Database
Option Explicit

Public Type GUID
  Data1         As Long
  Data2         As Integer
  Data3         As Integer
  Data4(0 To 7) As Byte
End Type

Private Declare Function CoCreateGuid Lib "ole32.dll" ( _
  ByRef pguid As GUID) As Long
  
Private Declare Function StringFromGUID2 Lib "ole32.dll" ( _
  ByRef rguid As Any, _
  ByVal lpstrClsId As Long, _
  ByVal cbMax As Long) As Long



Public Function GetGUIDString() As String

' Create a GUID and return its string representation.
'
' 2002-12-15. Cactus Data ApS, CPH.

  ' Length of GUID string per definition.
  Const clngGUID    As Long = 38
  ' Length of buffer with added space for zero terminator.
  Const clngBuffer  As Long = clngGUID + 1
  
  Dim udtGuid       As GUID
  Dim strGUID       As String * clngGUID
  Dim abytGUID()    As Byte
  
  ' Dim byte array.
  abytGUID() = String(clngBuffer, vbNullChar)
  ' Create GUID.
  If CoCreateGuid(udtGuid) = 0 Then
    ' GUID was successfully created.
    If StringFromGUID2(udtGuid, VarPtr(abytGUID(0)), clngBuffer) = clngBuffer Then
      ' GUID was successfully copied into byte array abytGUID in Unicode.
      ' Convert byte array to Ansi GUID string stripping zero terminator.
      strGUID = abytGUID
    End If
  End If
  
  GetGUIDString = strGUID

End Function

Open in new window

/gustav
0
 
LVL 5

Author Closing Comment

by:eantar
ID: 39216948
Nice. Thank you for that.

E
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39216972
You are welcome!

/gustav
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GA Ribbon creator 9 52
Intermittent OleDbConnection Error 20 42
What does "Between" mean? 6 34
2 Global Vars, 1 List Box 4 23
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

740 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