Linked Table doesn't allow Edits (is there a way to allow them)

[hardhitter06]

I have a linked table to a Notepad document in Access 2003.

I need to add an additional field (an autonumber) and change the format of a time field, but I cannot since the table is linked.

Is there a way for me to make these changes while the table is linked or is it possible to unlink the table, make the changes, and then relink it back to this notepad document?

Any insight would be great. Thank you.

 

[HiTechCoach]

Normally you can not edit the design of a linked table from the front end. You will need to go to the back end file and make the changes.

I would exit the front enf first before trying to edit the back end file.

 

[hardhitter06]

It's a Notepad document with text seperated by commas which respresent different fields. Editing this document is not possible (it comes from another source).

 

[boblarson]

It's a Notepad document with text seperated by commas which respresent different fields. Editing this document is not possible (it comes from another source).

You just said:

I need to add an additional field (an autonumber) and change the format of a time field, but I cannot since the table is linked.

but then you say you can't edit this document? Then you have to use the document to MAKE A TABLE and import the data if you want to add a field or add information. A linked document is just that LINKED, you can't change it, or add information to it, or add fields.

 

[hardhitter06]

Ok let me explain further.

The notepad document is linked to a Web Form...which retains the submissions from the end users. It is in plain text, and to represent a new field, a comma is used. The program is called Form to File and it does this automatically.

For example, Name, Address and Phone would look like this in the notepad file:

John Smith,1400 Washington,555-5555

If someone doesn't fill out an address:
John Smith,,555-5555.

Since this file updates on its own, and then I use Access to view the data, there is no place for me to create an autonumber field...unless I did on the webform (which i'm not sure how to do, or if it would even increment right if it could be set up). Like if someone started to fill it out and then closed, while some one else had it opened..and they submitted, I'm not exactly sure how that would work.

With that said, it isn't THAT important for me to change the design of my web form. This also wouldn't let me edit the time field.

So that's why I am asking if I can actually change the Access table, some how unlinking it, n then relinking it...or trying something completely different

 

[boblarson]

So that's why I am asking if I can actually change the Access table, some how unlinking it, n then relinking it...or trying something completely different

And let me reiterate. The linked table in Access is not really a table in Access. It is your text file. So, you have to make the change in the text file for it to work. Otherwise you can't use a LINKED file but you would have to IMPORT the data FROM the text file INTO an Access table and then you would have to keep deleting and importing data, or appending it somehow in order to work. But you can't have both - the linked document AND change something in that "table" that appears when linking because it really is the document and not a table.

 

[hardhitter06]

Ok thank you for the clarification

 

[Mr. B]

hardhitter06,

Have you considered having another table as your working table. This table would have the autonumber field and the date field formated the way you want it, and then use and append query to append the data in the linked table to your working table.

Just my thoughts.

 

[hardhitter06]

Mr. B, Thanks for your response.

I haven't but that sounds like a great idea!

Could you explain further on how I would go about setting this up.

Basically, I would create a second table (the working one), with all of the same fields as my linked table and just add an autonumber field and change the format of my Time field??

Also, I'm not really sure how to set up an Append Query, could you give me some direction on how to do that once I created this working table?

Thanks again,

Josh

 

[Mr. B]

Actually it is fairly simple to accomplish this. First just do a normal import of your data to a new table. (You may already have this step completed since you have been attempting to get this done.) Delete all records from this table. Add the new AutoNumber type field and format any fields that you need formated.

Now you can do the import and import the records to the existing table you just created. You can also do this programatically.

After the data has been successfully imported, you can then create the Append type query. To create this query, first create a new query. Select the "working" table that you created in the first step as the data source for the query. Display all appropriate fields from this table in the query. Next, from the Query Design toolbar, click the "Query Type" option button and select the "Append Query ..." option. You will be prompted for the table into which you want the new records to go. You may need to match fields from one table to the other for the actual match for the append process. Just save your query and you can use it each time you need to import the data and then append it to your final table.

 

[hardhitter06]

Ok...thank you for the thorough instructions.

Looks easy enough...I have one additional question though.

I'm trying to make the lives of the end user as easy as possible. I have a Command Button on my main form that the end user clicks to run a query that is pulled from the working table.

Is there a way to set this button up so when it is clicked, it will append my working table to my new table, and then run the query of my date search?

Because if i understand correctly, running the apend query will just move the data from the working table to the new table.

I'm not sure if you can add a search to the append query but it is important to note that before this date search is ran, I need all of the new data to be uploaded to this new table.

I hope this makes sense.

 

[hardhitter06]

Also...something isn't right.

When I go to create a new query, I first pick the fields from the new table (working) I just created. This is the table with the Autonumber field.

I then go to Query Type-->Append Query and it asks me to link a table to it. The only table that shows in this drop down box is the new table (working) I have just created. It does not show the linked table...

Is this not possible or am I doing something wrong?

 

[Mr. B]

I may have missed something in your description of the issue.

You have created a link to a text file that is updated from a Web site. You are viewing the info from that text file in Access. You need to import the data from the text file. You want this date to be formatted when it arrives in Access.

Questions:
Does your "working" table (and I am assuming that the "working" table is the one that will ultimately hold all of your data) already have a AutoNumber field? If so, then you do not necessarily need on in the new table that will receive the data from the text file.

This is where I think I many have misunderstood your situation. From your description of your current status, you already have a linked table (linked to the text file). All you need to do is create the Append type query that will append the data from your "linked" table to your new"working" table. I am assuming that your existing working table has the formatting for the date fields and the AutoNumber field.

Try just using the append type query to append the data from the "linked" table to your existing table.

I appologize if I got it wrong and got you confuse.

Also, let's get the data into the table correctly before concerning ourselved with helping the users. We will need to know that there is new data and create some code to do the import and append with one click of a button, but is can be done.

 

[hardhitter06]

Ok, no worries, let me break it down to you a little better.


Background info...the behind the scenes dont really matter that much besides there is a Notepad document linked to this Access Database. So depending on whatever is in this notepad document (which changes daily), the databases main table (the one and only table that is linked) reflects the document. The only reason why you suggested setting up an append query and adding this new working table was to help transfer a Autonumber to each of my records. (along with changing the format of one of the fields)

So this is where i am at:

Linked Table is missing Autonumber and the Time Field Format.

On the working table, I have all of the same fields from the linked table with the addition of the Autonumber field and Time field Format is different.

I created the Append Query with the Link Table Apending to the New Working Table. So all the fields match up except the link table has no autonumber field (never has). So i run the query and it sends the records into the new working table (works). Now I still have no autonumber, but the records transfer fine. [but this hasn't solved anything]

So I add the Autonumber field into the query from the New Working Table and it doubles the records being sent over and doesn't autonumber them right. I may be doing something wrong here...

So this is where I am stuck..I hope that helps get us on the same page.

 

[Atomic Shrimp]

Is there any field (or combination thereof) in the original text file table that uniquely identifies each record?

 

[hardhitter06]

And i just read my post before yours..I should be apoligizing...it makes no sense.

I take the Linked Table fields and Append to the New Working Table. The "append to" fields have one extra field (autonumber)..which i think is causing this to not matchup/work right??

 

[hardhitter06]

Atomic...

There is a Date and Time field so that would be unique if combined..

 

[Mr. B]

hardhittero6,

The AutoNumber field only needs exits in you final working table. If you go there and create the AutoNumber field, it will automatically be populated. Also, as you append the new records, the AutoNumber field will be populated.

 

[hardhitter06]

Right I know..so your saying to append the files to the working table...then add the new field "autonumber" and everything will be good?

Sounds like it will, but when I need to append the table again (which will be once a day), I'll have to delete that autonumber field, append, and then add it back in?

Am i understanding correctly...if so, that is not feasible

 

[Mr. B]

hardhittero6,

The AutoNumber field only needs exits in you final working table. If you go there and create the AutoNumber field, it will automatically be populated. Also, as you append the new records, the AutoNumber field will be populated.

As stated, just append new records. The AutoNumber field will automatically be populated for each new record that is added.