Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Replication ID in New Records

Posted on 2013-06-03
6
Medium Priority
?
958 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
  • 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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 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 52

Expert Comment

by:Gustav Brock
ID: 39216972
You are welcome!

/gustav
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 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