• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

Should I import or Link

So we have FileMaker Pro here and it holds our equipment inventory, however there are a few fields that I have been wanting to add to it but the DB Administrator won't add them.  Keep getting the I can't add anything once it is created,  I am sure they could, but either don't want to or they need to run it through a CCB.  

So I can export the equipment inventory to excel, which is great.

But I want to link or import it into access and add the fields we need there.

So here is what I am asking:
Would it be easier to link the spreadsheet to the database and if so how would I add my extra fields?
If I have to import it I can add the field there but how would I update the data from FileMaker pro each time I updated the imported spreadsheet without affecting the fields I have added?

Suggestions?

John
0
John Sheehy
Asked:
John Sheehy
  • 11
  • 7
  • 3
  • +2
9 Solutions
 
xtermieCommented:
Linking sounds better to me, but you should create an Excel sheet that will act as the "intermediate" table between the exported data and your additional data.  You will be able to create this after the first time you export the data from your database and insert the extra columns.

After that, using ODBC link the Excel table to the database, create the LOOKUPS necessary to retrieve the extra data and don't forget to update your intermediate table (sheet for lookups) everytime new data is needed.
0
 
Craig YellickDatabase ArchitectCommented:
How many records and how many fields are in the exported spreadsheet? How often are records added and deleted in the source database?

I could see using either a linked or imported table depending on circumstances. Linked is more convenient process-wise but can be very slow for large numbers of records. Importing gives you more control and faster performance.
1
 
xtermieCommented:
Craig is right that you should consider the number of records.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
John SheehyCommunications EgineerAuthor Commented:
So I should export the data from File Maker Pro.  Copy and rename the file and add my extra fields then link that table to the access database?

The issue I see with Linking is the data can not be updated.  The extra fields would need to be updated at least once a quarter.

I have written code before where once the file was imported into it's own table the information was then copied to the new table.  Which worked really well.  The issue there came in is when the table being imported didn't have the same fields as the the table it was imported into.

So for example:
Let say I import 5 records and then copied those records to the working database and updated the fields that were added,  Well the next quarter comes and I do the same process again only this time one record was deleted from File Maker Pro and one was added.  The import doesn't care and doesn't distinguish between added/deleted.

Suggestions?
0
 
John SheehyCommunications EgineerAuthor Commented:
We are talking 3000 records and 15 fields
0
 
John SheehyCommunications EgineerAuthor Commented:
I guess the hard thing is I am wanting to add extra data that is not in the File Maker Pro database. which would make updating data in the standalone database harder.  Unless no data is ever deleted from the FileMaker Pro version.  Something to ask though.
0
 
Craig YellickDatabase ArchitectCommented:
Regardless of import versus link, the Access drivers for Excel can make some bad decisions about data types and generally make things miserable. Before going much farther, do some trial imports and make sure the data comes over cleanly. Look for null values in records where you know there's data in the source system. Excel is my last choice for data xfer. If your DBA can generate a tab-delimited file that's the best way to go.

Dang, I've got a conference call starting up... will check on this thread later.
0
 
John SheehyCommunications EgineerAuthor Commented:
I can do tab delimited with no issue.  I had no issue importing the data a few times straight from excel.  Either or works.  But I will go with Tab for now to lessen the chances of an issue happening.
0
 
Craig YellickDatabase ArchitectCommented:
3,000 rows x 15 fields should not pose a performance problem for a linked table.

My first reaction is to create a parallel table in Access that carries the primary key of the equipment record, along with the additional fields you want to track.  When you get an updated file you'd first check for new keys and insert records to your parallel table. Deleted records can be detected and processed as well.

This should work whether you link to the equipment file as-is or import it. Linking is easier.
0
 
John SheehyCommunications EgineerAuthor Commented:
Linking doesn't allow you to update the records from within access, does it?
0
 
Craig YellickDatabase ArchitectCommented:
Updates over links are possible, it depends on the file type. Do you need to be able to update the fields that are stored in the equipment app?
0
 
John SheehyCommunications EgineerAuthor Commented:
I do not, those changes will actually be done in File Maker Pro.
The only changes I need to make will be to the fields that I need to add after the first import.
0
 
Craig YellickDatabase ArchitectCommented:
Read-only, that's perfect. A linked table should do the job with less hassle.
0
 
IrogSintaCommented:
Linking directly to the FileMaker tables from Access would be a better solution.  That way changes to your data in Access are reflected automatically in FileMaker.  You would do this via and ODBC driver.  Problem is, you need to have ODBC Sharing enabled in FileMaker but it sounds like your db admin isn't very willing to help.  Here's a link from FileMaker support that shows how to enable this.  It basically says to open the ODBC/JDBC Sharing Settings dialog box and enable ODBC/JDBC.
Edit menu > Sharing > Enable ODBC/JDBC.
They would then open up the ODBC Data Sources program and add FileMaker along with its security credentials.  Then you can link directly to any of their tables from Access.  However, without your db admin's help, this is all academic.

Ron
0
 
John SheehyCommunications EgineerAuthor Commented:
Great idea about linking straight to File Maker.

Linking is great but I still need to be able to add my 5 additional fields and how would they be updated.  Query maybe?
0
 
Craig YellickDatabase ArchitectCommented:
Once linked to an external table source, the process is identical regardless of the driver. Linking directly FileMaker Pro would be slick if it is supported. I have no experience with it.
0
 
John SheehyCommunications EgineerAuthor Commented:
I will work all suggested solutions today and let you all know this afternoon (CST) of the outcome.

Thanks
John
0
 
psteffCommented:
The thing I did not notice in this thread is – are the 5 extra fields totally specific to each individual record or are there groupings (such as all equipment ID’s starting with “c” get certain things entered into the extra fields, ID’s starting with “f” get another set).

Depending on what the data really looks like and how the extra five fields relate to the original data, I would think either Excel or Access would pretty much equally do the job – but I don’t think I would use both.  I would just try to use either Access for all of it or Excel for all of it.

An option that I did not see mentioned is just to link (or import) the FileMaker Pro (FMP) table into Excel in a table structure.  In addition, you would have a table(s) in another worksheet in the same workbook with the information needed to fill in the other five fields.

If the five fields are totally specific to each individual record, you would have one table with the ID number (or some unique number specific to the record) and the information for the other five fields.  You would then do a vlookup to this table from within the imported FMP table to populate the extra five fields.  You could do conditional formatting in the FMP table if you want to quickly see which records are not in your extra table and then go to add the information into your extra table.  With only 3000 records and 15 fields, I don’t think you would have a performance issue.

If the information for the extra five fields are such as in the second case above in my first paragraph (some type of grouping based on some type of data in the original file), then you would need multiple extra tables with the appropriate index/reference field for the FMP table to do the vlookup on.
0
 
John SheehyCommunications EgineerAuthor Commented:
So here is where I am at:

I can not link to the FileMaker Pro DB.  The SA says no way.
I can export the records to an excel workbook/sheet and I can import all that data into access with no issues

The five extra fields have no info yet.  But they will

The issue we are running into is when I need to trace a cable I have to find the drafter and have them pull the drawings so I know where the cable is supposed to go. Which is a lot of time a pain because they are so busy and they have very little time to pull drawings.

We suggested that if we added the ports from the switch / router we could enter the port number and the cable ID attached to that port.  Then we could run queries to find where the cable runs to.  That is two of the fields the other three fields are POC, POC Phone and POC Location.  Because believe it or not the POC may not even be in the same state/country.

My only concern came in when updating the data from the FileMaker Pro side which does not have the five fields.  If a switch was deleted in File Maker Pro, Access would not know that.  It may just have to be a manual compare in a temp table and then copy into the main table.

John
0
 
psteffCommented:
From your table(s) for the extra five fields, you could do a vlookup with the “false” option to look for an exact match in the FMP table.  If it returns false, you know that the switch has been deleted.  This would be much quicker way that other types of manual comparing and simpler than setting up a temporary table.
0
 
John SheehyCommunications EgineerAuthor Commented:
What is this vlookup you speak of?  I have never heard of it before.
0
 
psteffCommented:
I don’t know if you have decided between Excel or Access.  The vlookup is in Excel.  If you are deciding on Access, the following is not relevant.

Syntax of the vlookup is
=vlookup(lookup_value, table_array, column_index_number, [range_lookup])

So, if your data imported from FMP starts in row 2, and you want the first extra field data in column P.  In cell P2 of your FMP table, you would enter a formula similar to:

=vlookup(a2, Sheet2!$A$2:$F$3000,2,false)

Where:

A2 (or B2 or whatever column) is your ID number or the value in the FMP table that will reference your extra table.
Sheet2!$A$2:$F$3000 is the address of your extra table – change to wherever the extra table is located
2 is the column of your extra table that your information is in (change as necessary)
False is to make sure you want to return the value of an exact match only

The above formula should be modified to reference wherever your specific data is located.

Once you have the first formula in P2 (or whatever cell), then copy the formula down and you will get the extra field.  Do a similar formula for the other 4 fields/columns.
0
 
Craig YellickDatabase ArchitectCommented:
Access VBA has an equivalent function, called DLookup. Though in this case DCount would work as well. Can also use a SQL statement. All can detect the non-presence of a key.
0
 
John SheehyCommunications EgineerAuthor Commented:
Both are awesome solutions.  I will try them out right now.
0
 
xtermieCommented:
Good comments n recommendations provided
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now