Solved

Modifying a MSAccess table from another MSAccess DB

Posted on 2016-10-18
8
65 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 20

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 20
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 20
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 20
ID: 41850184
you're welcome ~ happy to help
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 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