Modifying a MSAccess table from another MSAccess DB

I have a MS Access DB front end that uses data in a different MS Access DB. When I open the front end, I need for it check a table the other DB to see if a text field exists, and if it doesn't, add it. Both DB's are Access 2010.
dgravittAsked:
Who is Participating?
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
assuming the table is linked:
'~~~~~~~~~~~~~~~~~~~~~~~~~~ IsFieldInTable
Public Function IsFieldInTable(psTablename As String _
   , psFieldname As String _
   ) As Boolean
'True if the specified field exists in the specified table
'150630 strive4peace

   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      , tdf As DAO.TableDef
      
   Dim sName As String
   
   IsFieldInTable = False
   
   Set db = CurrentDb
   Set tdf = db.TableDefs(psTablename)
   
   sName = tdf.Fields(psFieldname).Name
   
   IsFieldInTable = True
   
Proc_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
  
Proc_Err:
'   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   IsFieldInTable"

   Resume Proc_Exit
   Resume
End Function

Open in new window

to add it, you will need to set a database object to the path\filename, then set tdf object to the tablename in that database, then add the field
Sub AddField2TableOtherDatabase()
'160118 strive4peace
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field

   Set db = OpenDatabase("c:\path\filename.accdb")
   Set tdf = db.TableDefs("MyTableName")
   Set fld = tdf.CreateField("MyFieldName", dbText, 50)  'if 50 is not right for size, change it
   tdf.Fields.Append fld
   'db.TableDefs.Refresh

   db.Close
   Set fld = Nothing
   Set tdf = Nothing
   Set db = Nothing
End Sub

Open in new window

0
 
dgravittAuthor Commented:
Thanks. I'll try this in the morning.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
dgravittAuthor Commented:
Can I add the second piece to the first under IsFieldInTable = False
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
not advisable
IsFieldInTable is a general procedure you send parameters to (so it would be in a general (standard) module)
you can add it to the calling program if IsFieldInTable is false

*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc.

IMPORTANT: do NOT name the module the same as any procedure. Give it a unique name like "mod_whatever" or "bas_whatever"

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up
Option Explicit ' require variable declaration

Open in new window

0
 
dgravittAuthor Commented:
that will work
0
 
dgravittAuthor Commented:
Thanks for the help
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.