Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Modifying a MSAccess table from another MSAccess DB

Posted on 2016-10-18
8
Medium Priority
?
75 Views
Last Modified: 2016-10-19
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.
0
Comment
Question by:dgravitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41848429
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
 

Author Comment

by:dgravitt
ID: 41848443
Thanks. I'll try this in the morning.
0
 
LVL 22
ID: 41848451
you're welcome
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dgravitt
ID: 41848457
Can I add the second piece to the first under IsFieldInTable = False
0
 
LVL 22
ID: 41848461
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
 

Author Comment

by:dgravitt
ID: 41848468
that will work
0
 

Author Closing Comment

by:dgravitt
ID: 41850020
Thanks for the help
0
 
LVL 22
ID: 41850184
you're welcome ~ happy to help
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question