Results 1 to 4 of 4
  1. #1
    prophecym is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    5

    Importing existing Autonumber fields

    Hello everyone,

    I have been using an online database and having issues with it. I might have to migrate my existing database to Access. The problem is I don't know how to migrate my existing autonumber fields into Access without messing my database up. I need to be able to use my existing autonumbers as an autonumber data type.



    Any ideas ?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you can't change a field to an autonumber after it's been created, but you can manipulate number fields to be autonumbers by append operations after an import has been done.

  3. #3
    prophecym is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    5
    Quote Originally Posted by ajetrumpet View Post
    you can't change a field to an autonumber after it's been created, but you can manipulate number fields to be autonumbers by append operations after an import has been done.
    Can you explain how to do that ?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    here's an example that i wrote:
    Code:
    Function AutoNumStart(tblName As String, _
                          fldName As String, _
                          nStart As Long)
    
    On Error Resume Next
    
    '******************************************************************************
    '_____________________________________________________________________________*
    '                                                                             |
    'This function assumes you have a table setup with an autonumber field in it. |
    'NOTE: The autonumber field cannot contain an index of any kind or the        |
    '      function will error.  Code can be modifed to create a primary key      |
    '      and new index at the end of execution.                                 |
    '_____________________________________________________________________________|
    '                                                                             *
    'Author: Adam Evanovich                                                       *
    'Date: 3/20/2010                                                              *
    'Purpose: Resets the start value of an autonumber field.  This function can   *
    '         be used at any time and with any amount of data already present.    *
    '                                                                             *
    'Arguments:                                                                   *
    'tblName > Your table.                                                        *
    'fldName > Name of the autonumber field.                                      *
    'nStart > New start value of the autonumber field.                            *
    '                                                                             *
    '******************************************************************************
    
    Dim temp As String
    Dim db As DAO.Database
    Set db = CurrentDb
    
    temp = db.TableDefs(tblName).Name
    
       If err.Number = 3265 Then
          MsgBox "Table does not exist!"
             GoTo Exit_Handle
       Else
          temp = db.TableDefs(tblName & "2").Name
             If err.Number = 0 Then
                db.TableDefs.Delete (tblName & "2")
             End If
       End If
    
    On Error GoTo Err_Handle
    
    DoCmd.CopyObject , tblName & "2", acTable, tblName
       db.TableDefs.Refresh
       
    Dim tbldef As DAO.TableDef
    Set tbldef = db.TableDefs(tblName & "2")
    
       With tbldef
          .Fields.Delete (fldName)
          .Fields.Append .CreateField(fldName, dbLong)
       End With
    
          DoCmd.SetWarnings False
          
          DoCmd.RunSQL "INSERT INTO [" & tblName & "2] ( [" & fldName & _
                       "] ) VALUES (" & nStart - 1 & ")"
          
          DoCmd.RunSQL "INSERT INTO [" & tblName & "] ( [" & fldName & _
                       "] ) SELECT [" & tblName & "2].[" & _
                       fldName & "] FROM [" & tblName & "2] " & _
                       "WHERE " & fldName & " = " & nStart - 1
          
          DoCmd.RunSQL "DELETE * FROM [" & tblName & "] " & _
                       "WHERE [" & fldName & "] = " & nStart - 1
                       
          DoCmd.SetWarnings True
    
    DoCmd.DeleteObject acTable, tblName & "2"
    
    Exit_Handle:
       db.Close
          Set db = Nothing
             Exit Function
             
    Err_Handle:
       MsgBox err.Description
          Resume Exit_Handle
    
    End Function '//LL
    so you can see there, that an autonum field is being used as a target for an append operation to throw values from a LONG field to it. what I was suggesting was that you import your field into a long type field, then do something like what you see above into your own autonumber target field.

    make sense??

    the other option of course, would be to modify your front end interface to simply put:
    Code:
    dmax() + 1
    into your long data type field of the imported table whenever there would be a new record entered. in general, that's the same thing as what an autonumber does, for user purposes anyway.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 09-01-2010, 02:09 PM
  2. Help with existing database
    By byoung11 in forum Database Design
    Replies: 2
    Last Post: 06-29-2010, 06:21 PM
  3. Exporting XML with existing XSD
    By royf in forum Import/Export Data
    Replies: 0
    Last Post: 06-20-2010, 10:48 AM
  4. XML file not importing all fields
    By ButlerEagle in forum Access
    Replies: 0
    Last Post: 05-17-2010, 10:39 AM
  5. Resetting autonumber when importing
    By bullwinkle55423 in forum Access
    Replies: 3
    Last Post: 06-30-2009, 11:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums