Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72

    Question adding primary key to a bulk of files

    Hi,

    I have hundreds of mdb files with no primary key.
    I would like to set the first field of these tables as the primary key.
    All these files have the same structure : they only contain 1 table (named "table1" I think) and the 1st field which should be the primary key is always titled "ID".



    I used to have a small access app which did exactly this, but I lost it with my hard drive :-(

    I know it was just a few lines of code, but I don't know how to do that.

    Can anyone help me please?

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    cant you just edit the design, select the field, and click KEY button?
    (no code needed)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Am away from the office but see this link for the sql to create a primary jey
    https://www.w3schools.com/sql/sql_primarykey.asp
    You’ll need to loop through the dbs and tabledefs to execute the sql

  4. #4
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by ranman256 View Post
    cant you just edit the design, select the field, and click KEY button?
    (no code needed)
    Hi ranman256,

    I can't do that for each file one by one. This is why I would like to use an acess file which does this automatically with the whole bulk of files.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, key the access table.

  6. #6
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by Ajax View Post
    Am away from the office but see this link for the sql to create a primary jey
    https://www.w3schools.com/sql/sql_primarykey.asp
    You’ll need to loop through the dbs and tabledefs to execute the sql
    Hi Ajax, thank you for helping.
    Unfortunately, I am not a coder and I don't know how to use the content of this link.

  7. #7
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    In my archive folders, I found the beginning and the end of the code I am looking for. But this is not the final version, and I miss the middle of the code...
    Here it is in case it helps :

    Code:
    Option Compare Database
    
    '--------------
    Public Sub CompactAll()
    '--------------
    cycleThruAllFilesInDir "G:\Nico\Desktop\Test\"
    End Sub
    
    
    
    
    '--------------
    Public Sub cycleThruAllFilesInDir(ByVal pvDir)
    '--------------
    Dim FSO, oFolder, oFile, vFile
    Dim sTxt As String, sFile As String
    Dim acc As Access.Application
    Dim control As Office.CommandBarControl
    Dim bRun As Boolean
    Dim i As Integer
    
    
    
    
    On Error GoTo errGetFiles
    
    
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvDir)
    Set acc = New Access.Application
    
    
    
    
    For Each oFile In oFolder.Files
         bRun = False
         If (InStr(oFile.Name, ".accdb") > 0) Or (InStr(oFile.Name, ".mdb") > 0) Then
                  'verify its a db file
              If (InStr(oFile.Name, ".accdb") > 0) Then
                i = InStr(oFile.Name, ".accdb")
                If i = Len(oFile.Name) - 5 Then bRun = True
              Else
                i = InStr(oFile.Name, ".mdb")
                If i = Len(oFile.Name) - 3 Then bRun = True
              End If
              
    
    XXXX missing code XXXX
    
    
         End If
    Next
    
    
    
    
    endit:
    Set oFile = Nothing
    Set oFolder = Nothing
    Set FSO = Nothing
    Set acc = Nothing
    Set control = Nothing
    
    
    
    
    MsgBox "Done"
    Exit Sub
    
    
    
    
    errGetFiles:
      MsgBox Err.Description, , Err
      Resume endit
    End Sub
    There may even be some mistakes in this code I found, I don't know.
    I think the missing code is the part where it added the primary key.

  8. #8
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by ranman256 View Post
    yes, key the access table.
    Sorry, I don't understand what you mean.

  9. #9
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    I guess we should add the code from Ajax's link :

    either something like :

    Code:
    ALTERTABLE Persons
    ADDPRIMARYKEY (ID);
    or

    Code:
    ALTERTABLE Persons
    ADDCONSTRAINT PK_Person PRIMARYKEY (ID,LastName);
    but I don't know how to do that precisely.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    All these files have the same structure : they only contain 1 table (named "table1" I think)
    How come you are unsure of this? Wouldn't a simple, random selection and review clarify?

    Tell us about the hundreds of mdb files with no primary key.

    mdb files are Access databases in a format used with Access 2000-2007.
    Access databases are typially relational databases
    mdb files usually contain Tables. Tables have a specific structure.
    Relational tables should have a primary key.

    Why exactly do you have to "modify" all of these files?

    If these files (databases/tables) contain data you may not be able to create PKs from existing fields. The fields may contain duplicates which are not allowed with PK.

    I'd like to hear more about why this has to be done.

  11. #11
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Hi orange,

    Quote Originally Posted by orange View Post
    How come you are unsure of this? Wouldn't a simple, random selection and review clarify?
    you are right, I did not know the table's name was important. Each mdb file contain 1 table named "Table1".



    Quote Originally Posted by orange View Post
    If these files (databases/tables) contain data you may not be able to create PKs from existing fields. The fields may contain duplicates which are not allowed with PK.
    No, the "ID" field which is the 1st one in every table doesn't contain duplicates. This is why I want this field to be set as Primary Key.

    Do you know how to code the access macro to achieve what I need?

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  13. #13
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by orange View Post
    Why do you have hundreds of such files?
    why not? does that matter when it comes to adding a primary key?

    Quote Originally Posted by orange View Post
    How can/do you tell which is which?
    I know which is which because they have a name.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ??
    You have given minimal or sub minimal info.

    The logic generally would be along these lines:
    (within a new database)
    -identify the folders where these "mdb files" exist
    -for each folder, get a list of folders with such files {A}
    --for each mdb file {B}
    ---open the mdb as a second database
    ---review the (non MSys tabledefs) to see what really exists in 2nd db
    ---if there is a Table1 with field ID then
    -----run code to create the PK (and verify somehow)
    ---[[[I found this code but have no idea if it works for your situation]]]
    ---if there is no such table then provide an error msg
    ---if there is no such field then provide an error msg
    ---get the next mdb file and repeat{B}
    --go to next folder and repeat {A}

    Good luck.

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    See the attached file - I used your initial file (the csv importer/mdb maker). I have run it against two of your files and B.mdb does have duplicates in ID so you get an error. I am trapping the errors and continue through the list so at the end you will get a list in the new table tblPrimaryKeyError of all the files where you need to remove the duplicates.

    Cheers,
    Vlad
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing a bulk of files from different folders
    By jangobango in forum Access
    Replies: 10
    Last Post: 03-07-2016, 04:30 PM
  2. Importing multiple XML files in bulk via append
    By Awesome! in forum Import/Export Data
    Replies: 10
    Last Post: 03-12-2015, 09:09 AM
  3. How do you bulk attach files in Access?
    By newyorkyankee in forum Access
    Replies: 5
    Last Post: 05-11-2012, 01:06 PM
  4. Adding a record with a new primary key
    By thefairman in forum Access
    Replies: 2
    Last Post: 12-21-2011, 07:03 PM
  5. Replies: 1
    Last Post: 08-03-2010, 01:33 PM

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