Urgent Plz: USER DEFINED VALUES IN THE AUTO NUMBER FIELD (MS ACCESS)

Urgent Plz: USER DEFINED VALUES IN THE AUTO NUMBER FIELD (MS ACCESS)

Post by Ben » Sun, 01 Jun 2003 22:59:22



I'm not very familiar with ADOX, so I can't point to your
error, but I may be able make a suggestion to save
further headaches in the future. In my experience,
autonumber fields tend to be, well, a royal pain.
Especially if you need to migrate the data from Access to
SQL, or you need to support multiple platforms.
At my place of business we generate key values randomly
and use a long integer field for the PK. We have a
general function to do this:
Public Function GenerateRandomIDNumber(pDBConnection As
ADODB.Connection, pTableName As String, pFieldName As
String, Optional pKeepDBConnOpen As Boolean = False) As
Long
    Dim lngRandomID As Double, bolWasClosed As Boolean
    Dim rstRandomTable As ADODB.Recordset
    On Error GoTo GenerateRandomIDNumber_Err
    if pDBConnection.state = adStateClosed then
       pDBConnection.open
    end if
TryAgain:
    Set rstRandomTable = New ADODB.Recordset
    rstRandomTable.CursorLocation = adUseClient
    Randomize
    lngRandomID = Format(((999999999#) * Rnd) + 1, 0)
    'This checks to make sure the random ID doesn't
already exist
    rstRandomTable.Open "SELECT " & pFieldName & " FROM "
& pTableName & " WHERE " & pFieldName & "=" &
lngRandomID, pDBConnection, adOpenDynamic,
adLockOptimistic, adCmdText
    If rstRandomTable.EOF = False And rstRandomTable.bof
= False Then
        'already exists try again
        rstRandomTable.Close
        Set rstRandomTable = Nothing
        GoTo TryAgain
    End If
    rstRandomTable.Close
    Set rstRandomTable = Nothing
    GenerateRandomIDNumber = dblRandomID
    If pKeepDBConnOpen = False Then
       pDBConnection.Close
    End If
    Exit Function
GenerateRandomIDNumber_Err:
    GenerateRandomIDNumber = 0
End Function

HTH

>-----Original Message-----
>hi,

>I am using Access 2000

>Filed Name: autono (It's an AutoNumber field)

>I want to assign an autonumber in this field. is it
possible ?

>I was try to ADOX, but i have received a error message:
>Run-time error '3265':
>Item connot be found in the collection corresponding to
the requested name
>or ordinal.

>    Dim cat As New ADOX.Catalog
>    Dim col As ADOX.Column
>    Dim ctno As Long

>    Set col = New ADOX.Column
>    Set col.ParentCatalog = cat

>    ctno = 5 ' user define value
>    cat.ActiveConnection = cn

>    Set col = cat.Tables("TableName").Columns("autono")

>    With col
>       .Properties("Seed").Value = ctno ' Assign the
autonumber filed
>    End With

>    Set cat = Nothing: Set col = Nothing

>Please let me know, How it can be done.

>Thanks in Advance!

>Zahid Qamar
>http://www.zahidq.cjb.net

>.

 
 
 

1. MS Access and user-defined functions in MS SQL

I have a database that has been converted from MS Access
to MS SQL and the users still use Access to query data.  
The data is healthcare related and a typical query might
analyse occupied bed days during a given period using the
following function:

Function range(admit, discharge, date_beg, date_end) As
Long
If IsNull(discharge) Then discharge = date_end + 1
If discharge > date_end Then discharge = date_end + 1
If admit < date_beg Then admit = date_beg
obd = DateDiff("d", admit, discharge)
End Function

Can I convert this function to make it available in MS SQL
(as a stored procedure ?) so the MS Access users can use
it when they write their queries in Access and thus get
the full benefit of the server doing all the work?

Many thanks,  Stephen

2. Bad working of 'like' statement on column with collate

3. Auto-update a number field w/ Auto-Enter Calculation

4. Global temp table issue, occasional invalid object name error

5. ADO, MS-ACCESS, AUTO NUMBER

6. PLEASE HELP, SERIOUS PROBLEM

7. Ignore Auto-number fields in Access?

8. SQL 7 log reader error

9. Ignore Auto-number fields from Access?

10. Auto Number Field in ACCESS table

11. Grouping on User-Defined Values (Access vs. SQL)

12. I need DECLARE user-defined functions into MS-Access

13. User defined functions in MS Access