Solved

Replication ID in New Records

Posted on 2013-06-03
6
630 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
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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
Comment Utility
Nice. Thank you for that.

E
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now