Not sure if I want to Unlink? Need control of my data

[Dchall_San_Anto]

First off, I'm not computer challenged at all, but I am completely database challenged. Even the "Databases For Dummies" books throw me for a loop, so please be patient with the way I ask this question. And if I don't understand the replies, I'll do my best to look up the words, but this stuff really is not my bag. I'll try to explain what I want to do and what's in my way.

I set up a database on my Macintosh using FileMakerPro several years ago. "Several years ago," are the most significant words in that sentence. The DB was for my wife to keep track of members of a club she started. Now she's drifting away from the presidency of the club and also drifting away from the Mac. So I'm trying to migrate the FileMaker DB to MS Access so I can hand a working database off to the next officers.

I have a version of FileMaker which never heard of Access, so I exported all the records to Excel. I opened the Excel file on the PC, cleaned up some of the unused fields, and resaved it with a new name. I was able to open Access and link to the cleaned up Excel file with no problems. At my stage of sophistication, I consider this a major accomplishment!! I realize I have lost all the calculated fields I had, but there weren't that many that I can't rewrite them - I'll cross that bridge later. But first I have other problems.

I managed further to develop a form to use to enter new data and, I thought, to change data in the original file. A couple of the fields need to be changed from 'text' to 'memo,' or even from number to text; however, when I tried to do that, I got an error message that says my table is a linked table with properties that cannot be modified. I tried unlinking the table from the database, but then I had no records - no nothing, actually.

Here's what I think I want: I want the original fields and data from the Excel spreadsheet in a form that I have full control over from within Access. I can query (another major accomplishment, but the wizard helped a lot) and get the entire database. Is there a way to save that query such that it opens as a database (or table or whatever it's called) such that I can change that?

 

[Pat Hartman]

You don't want to leave the data in Excel. Once you have exported it from FileMaker and cleaned it up, IMPORT the spreadsheet into Access. This will copy the data into an Access table whose attributes you can then change as necessary.

To complete the process create a query that selects the columns you need and use that query as the recordsource for your form. As long as you haven't changed any field names, you can just open the form in edit view and replace the name of the linked spreadsheet with the name of the new query and your form will continue to work.

 

[Dchall_San_Anto]

This sounds like what I'm looking for. Thanks. Couple more questions.

As long as you haven't changed any field names, you can just open the form in edit view

So far, I think I can do that. Do you mean the "Design View," instead of "edit view?" Did I mention that I'm pretty dense on this stuff. The official database lexicon is trouble enough, please don't go using slang on me.

replace the name of the linked spreadsheet with the name of the new query

And I would do that how/where?

 

[Pat Hartman]

In design view, open the properties dialog for the FORM. On the data tab, change the RecordSource to your new query.

 

[Dchall_San_Anto]

Thanks again. That seemed to work except I cannot change the data types on the query. There seems to be a way to make a new table out of a query in the Access help section. My eyes are crossing right now from the late hour, so I'll check it out in the morning.

 

[Dchall_San_Anto]

I think I got it. I used the Help to look up Make-table Queries. I followed the directions, followed the false trail, came back to the main trail, skipped over the extraneous directions after trying them, and eventually got a table in the Table section that I can change the date types on. So in other words, it was a typical MS Help session.

Thank you for getting me to that point. Appreciate it.