Solved

Modifying a MSAccess table from another MSAccess DB

Posted on 2016-10-18
8
62 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
  • 4
  • 4
8 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 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 19
ID: 41848451
you're welcome
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:dgravitt
ID: 41848457
Can I add the second piece to the first under IsFieldInTable = False
0
 
LVL 19
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 19
ID: 41850184
you're welcome ~ happy to help
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

791 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