Creating a Unique Key on Import

[Amy E. Baggott]

I have an interesting situation. We load all of our exhibitors up to our web
site for listings and to allow them access to things like the Service Kit,
badges, housing, etc. Our third-party registration company has implemented
new security procedures that require two keys to be passed from our website
to theirs for an exhibitor to access their badges and rooms. The keys have
to be something the user can't change, as they have to match data we send
them when the exhibitor is first added to the show. We already have one, the
Master ID, which comes over from our association's main database and is the
Primary Key of my company table. We have decided to add a field for the
second key as a six-character random string. I have a function to generate
the string, which I use for their initial password, but it isn't set up to be
unique. I have a unique index on the field in the company table. However,
when I ran an update query to populate that field, I had to run it several
times before I had all the current records populated. I have a VBA import
program set up to pull new companies from our association's main database
(where all our contact information has to be stored by IT rules). What I
want it to do is for each record that is added, I want it to populate that
field by running my random string function. If it gets a string that's
already in use, I want it to keep trying until it gets one that isn't and
populate the field with that string. Any ideas on how to do that?

 

[PieterLinden via AccessMonster.com]

I have an interesting situation. We load all of our exhibitors up to our web
site for listings and to allow them access to things like the Service Kit,
badges, housing, etc. Our third-party registration company has implemented
new security procedures that require two keys to be passed from our website
to theirs for an exhibitor to access their badges and rooms. The keys have
to be something the user can't change, as they have to match data we send
them when the exhibitor is first added to the show. We already have one, the
Master ID, which comes over from our association's main database and is the
Primary Key of my company table. We have decided to add a field for the
second key as a six-character random string. I have a function to generate
the string, which I use for their initial password, but it isn't set up to be
unique. I have a unique index on the field in the company table. However,
when I ran an update query to populate that field, I had to run it several
times before I had all the current records populated. I have a VBA import
program set up to pull new companies from our association's main database
(where all our contact information has to be stored by IT rules). What I
want it to do is for each record that is added, I want it to populate that
field by running my random string function. If it gets a string that's
already in use, I want it to keep trying until it gets one that isn't and
populate the field with that string. Any ideas on how to do that?

Say you have a function, CreateRandomString() As String

Wrap the code in a Do Loop so that the process to generate the random string
runs until the generated string is unique. Something like...

Dim strPassword as string
dim intExists as integer

Do
strPassword = GetRandomString() ' stub for your code to create the
string
intExists=DCount("[UniqueID]", "UserList", "[Password] ='" &
strPassword & "'")
Loop Until intExists=0

' if you've gotten here, this must be a unique password, so assign it to the
record...
me.txtPassword=strPassword

or, if you're using a recordset...
rs.AddNew
rs.Fields("Password")=strPassword
rs.Update