Solved

check to see if field exists in table prior to linking

Posted on 2016-09-12
6
48 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 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
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!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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 …

707 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