check to see if field exists in table prior to linking

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?
LVL 4
Keyboard CowboyAsked:
Who is Participating?
 
bfuchsCommented:
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
 
bfuchsCommented:
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
 
Keyboard CowboyAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
If you add a field, it will not contain any values with which you can link with your local table.
0
 
aikimarkCommented:
0
 
Keyboard CowboyAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.