Solved

check to see if field exists in table prior to linking

Posted on 2016-09-12
6
45 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
  • 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 45

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 4

Accepted Solution

by:
bfuchs earned 500 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 45

Expert Comment

by:aikimark
ID: 41795093
0
 

Author Closing Comment

by:Keyboard Cowboy
ID: 41805461
thanks
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

713 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