?
Solved

check to see if field exists in table prior to linking

Posted on 2016-09-12
6
Medium Priority
?
50 Views
Last Modified: 2016-09-19
Access 2013 Desktop database using VBA
I have a front end and linked back end database.  
I have logic for the user to selectively change the linked backend database to another accdb file.
When the user indicates to link to a new backend database,
I want to first check to see if a particular field exists in the new database - if not, add it to the new database,
then proceed with the linking.

how do I determine the presence of a field in a database I haven't linked to yet?
0
Comment
Question by:Keyboard Cowboy
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 4

Expert Comment

by:bfuchs
ID: 41794929
Function ifFieldExists(FieldName As String, TableName As String) As Boolean
Dim rs As Recordset, db As Database ' DAO Vars

'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
'Set references by Clicking Tools and Then References in the Code View window
'Checks if Field exists.
'USAGE:     ifFieldExists  "FIELDNAME", "TABLENAME"

On Error GoTo NoField 'If there is no Field capture the error.

Set db = CurrentDb()

'If Field is there open it
Set rs = db.OpenRecordset("Select " & FieldName & " from " & TableName & ";")
        ifFieldExists = True
       rs.Close
       db.Close
Exit Function
NoField:
'If field is not there close out and set function to false
   Set rs = Nothing
         db.Close
   Set db = Nothing

     ifFieldExists = False
Exit Function
End Function

Open in new window

0
 

Author Comment

by:Keyboard Cowboy
ID: 41794946
I need to check in a unopened database which is not the currentdb.  Can I point the set db to a path for the accdb I want to check in?

If the field doesn't exists, I have the logic to add it  I believe using alter table.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41795033
If you add a field, it will not contain any values with which you can link with your local table.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 4

Accepted Solution

by:
bfuchs earned 2000 total points
ID: 41795044
Not sure but perhaps check this

http://software-solutions-online.com/check-if-field-exists-in-an-external-access-database-vba-automation/

BTW, if you have the code to link, you can link it first check with this and then remove the link..
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41795093
0
 

Author Closing Comment

by:Keyboard Cowboy
ID: 41805461
thanks
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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