export to Access

[emtodell]

Hi, I'm relatively new to FMPro, so pls. bear w/ me. And by-the-by, for all of you die-hard FMPro fans, it is Not My Decision to leave FMPro - that decision was made by our tech guy.

I'm using Windows 2000 Professional I have this straight-up db in 5.0 - nothing fancy about it, and I'm looking to export the records and get them into Access 2000. I do have a trial copy of FMP 5.5 and our university may actually be buying 5.5 in the very near future.

There are 58 fields and 4800 records in the db. When I export as .csv, the field names are eliminated (very irritating) and when I import the records into Access I wind up w/ the table and a table of errors. When I export as .dbf, the field names are truncated (not quite as annoying), and the fields themselves are limited to 254 characters (this may or may not be a problem).

Our tech guy started mumbling something about an ODBC driver for FileMaker 5.0. I'm not real familiar w/ ODBC and why having that driver would be helpful. Tech guy is wicked busy, so I'm trying to pitch in w/ my limited knowledge and willingness to learn. So, I'm looking for a clean way to get the data into Access w/o having to fix fields and errors.

Many thanks in advance,

Ellen

[email protected]

[Kurt Knippel]

What, you are having touble with Access? Your "tech" guy is too busy to help?

I am totally shocked. Bill Gates himself told me that Access is the most powerful and easiest to use database platform available.

You can try every kind of export out of Filemaker, they are all various text formats. I think that the Merge format includes the full field names as the first record.

Personally I would continue to use Filemaker until you "tech guy" gets Access setup and gets your data converted. This was his decision, let him get it working. I'd bet you'll be using Filemaker for far longer than this guy will be employed there.

[kraftyman]

If you only have one file, and less than 33 indexed fields then this is the solution for you. You will be done in < 1 minute

1. Enable sharing of the filemaker database via the local or remote data acces companion.

2.Leave filemaker running

3. go into "administrative tools" in the control panel

4. go into "ODBC data sources"

5. in the next dialogue click "add"

6. select filemaker pro as the odbc driver

7. put the EXACT name of the fmp database including the .fp5 extension

8. click apply

9. open accecc

10. create new database

11. select new table >>import table

12. select ODBC sources from the file type dropdown

13. the ODBC sources window will pop up ( just like the one you had in step5)

14. select the filemaker database you enetered before

15. click ok

16. All done

17. pat youself on the back

18. a raspberry to microsoft !

[emtodell]

Kraftyman,

Thanks for the help! Okay, now, how do I enable sharing of the filemaker database via the local or remote data access companion? We are on a local network if that helps. I did do all of the other steps, but the Filemaker db did not appear when I chose the ODBC source in step 13 & 14. So, I'm guessing that has something to do w/ not enabling the file sharing. I'm going to do some digging while I anxiously await your response. Thanks again.

Oh, under the ODBC Data Source Administrator, there are 7 tabs to choose from when I "add." Am I "adding" under "User DSN" tab? This is step 5.

Ellen

[bbaliner]

In File Maker go to file->sharing->select Local Data Access Companion

The ODBC driver should now appear.

If the ODBC driver still doesn't appear, pop in the FileMaker installation CD, and install the ODBC driver from there.

[emtodell]

bbaliner,

Thanks! I did that and now I can actually see my files, but even though I've gotten rid of the indexing in the filemaker dbs, Access keeps telling me that "There are too many indexes on table 'VtQoDefTemp0000'. Delet some of the indexes on the table and try operation again."

Harumph!

Ellen

[email protected]

[emtodell]

Okay, for anyone who cares...

I have finally connected my dbs using ODBC! The only way it worked is if I reduced the number of fields in my FMP to 32 fields. Kraftyman's message tipped me off as to the number of fields I could use.

Does anyone know why this is?

Ellen

[email protected]

[kraftyman]

The short answer is = limitation of MS Access

This might seem very weak compared to FMP, but remember that an access file is structured very differently to FMP

In FMP the file = data + layouts + scripts + other stuff is all together. There is no practical limit to any of these.

However, one FMP file only contains one lot of data. Related information is stored in individual files

In Access data is stored as individual tables, and is totally separate from the queries, forms, reports, scripting and other things. You can import /export any item indiviadually.

In Access you can have 32768 individual items, you can have multiple tables ( or lots of data) in one file. Each table can have up to 32 indexes. This is not a problem, because there would be few occasions when you might need to index so many fields in one table. It is usually better to split the data into related tables, and thre is no practical limit to the indexes you can have then.

When Access talks to FMP, it conects one table to one FMP file. This will work if there are <32 indexes in the FMP file. FMP indexes files to establish relationships, and enable value lists ( ?and other things). It is VERY EASY to exceed 32 indexed fields for this reason.

If Access finds >32 indexes, then it cannot split the table, and it just gives up.

Lesson 1 = FMP conforms to ODBC but only just. They make it a big sales push, but the reality is not that great. You will have much more success with ODBC when you are pulling data into FMP.

Lesson 2 = small FMP files are much easier to manage in this environement than big ones.

Lesson 3 = if you know you will be using other software to connect to FMP, be very careful in the original design of your database.

Lesson 4 = try if possible to separate data from interface ( very hard to do in FMP)

Hope this longer answer is useful

[emtodell]

Kraftyman,

Thanks so much for taking the time to explain, and yes, your explanation actually makes sense to me! I did wind up having to split up some of the fmp files, keeping a field called "recordno" with each split.

The next challenge is making the tables in Access relational, but tech guy said he'd help w/ that.

Thanks again,

Ellen

[kraftyman]

making "relational" tables in access is a snap,you just have to decide which field your primary key is this should be a field with UNIQUE information. Then you Access is VERY strict with this. You may expose the slackness of filemaker in this regard, and have to recreate a unique number. There is a table analyzer and splitter wizard to help you make such decisions. The simplest way to get unique ID's is to add an Autonumber field, and set this as the primary key. This is the default way Access handles ID's. There are problems with it if the data is multiuser/replicated, and other things are usually employed but this is beyond the scope of an FMP forum.... and...... make your tech dude earn his pizza!

[danjacoby]

Just out of curiosity...

kraftyman, when you write that "your primary key ... should be a field with UNIQUE information", does this mean that Access can't handle many-to-many relationships?

[kraftyman]

No, it simply means what it says. The value should be unique. This might be a numerical, or textual, or even a 128bit binary value ( which is used for replicating databases). If you already have some data, and tell Acess to make one of the fields the primary key, then it will check all the records and make sure the values are unique, and not do what FMP does ( FMP does not check existing records for integrity of uniqueness). IN fact it's a little more complex than that, because there are two "levels" of uniqueness. in one instance, you can simply index the data and set it not to allow duplicate values, in another instance you can also specify the "primary key" setting. The latter is used for very strict relational links that allow cascaded deletes and cascaded updates.

Many to Many relationships are handled in the traditional way of having a join table. This is far less flexible than the looser FMP model, but makes it much easier to get MSaccess talking to other databases.

[shkrebs]

Hi Kraftyman.

I know this is an old thread, but you seems to be one of the few in this forum who has knowledge of both Access and FM.

I have and old FM4 production application and a finance system based on Access tables. My client want to have his customer info ONLY ONE PLACE and he don't want to pay for a conversion (FM 2 MSA). I've been testing the ODBC on the FM6 and it's - well let's say - not very flexible but it works. I'm able to get data from the MSA tables into FM - so far so good. But I'm missing a feature like in MSA, where I can "link" to a external datasource, because even when I'm using the ODBC driver FM is making an IMPORT of the data. This means that my client have to make an new import every morning acording to keep his database updated / syncronized with the finance system. I know that FM is not using the same structure as MSA regarding tables, forms, queries etc. and maybe this is the answer/reason - I don't know - but maybe you have some experience you could share ? What is the best solution on this "import-problem" :??

Best regards

S