Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

check to see if field exists in table prior to linking

Posted on 2016-09-12
6
Medium Priority
?
53 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
 
LVL 1

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
LVL 1

Author Closing Comment

by:Keyboard Cowboy
ID: 41805461
thanks
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

636 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