Working with linked tables (1 Viewer)

brixie11 · Jan 29, 2010

Hi.

I have a problem with linked tables. I have a linked table and I want to add some fields to the data. I know I can't add new fields to a linked table ( because it's read only ), so I made a new table in my project. In that table I added a few new fields that I need for my application. So far I made relationship with linked table and my new table with key field, which is product code. So my question is how can I automaticly update the table with linked table and leave added field intact?

Regards, Brixie.

ghudson · Jan 29, 2010

Do you have permission to add the new fields to the source table?

brixie11 · Jan 29, 2010

Problem is that I would like to add attachment fields in the database for printing barcode stickers in packaging department. The source database is a Firebird and can not handle attacments.

Simon_MT · Jan 29, 2010

The barcode is just a font type so it can intrepret any field and convert it as a barcode format I use CIA Code 39 Medium Text Font.

Simon

brixie11 · Jan 29, 2010

The stickers are made of barcode, name of product, product code.... So there are pics ( JPG ).

Simon_MT · Jan 29, 2010

But the sticker originates from text so why can't the database emulate the barcode in reverse.

Simon

brixie11 · Jan 29, 2010

Ok. I will tell the whole project I'm working on. Some time ago our company got the database ( firebird ) for our products. It contains products, working orders, reports... Now I have to do application for packaging department which has to include printing stickers for the boxes, main boxes and palletes. Applllication must also have photos of packaging steps for the worker. Only way to do this is by the attachments in MS Access 2007. Since when firebird doesnt have this option I have to copy table to a new one and add attachment fields to it. In that case I have to copy or update new table with fresh data evry morning when database is opened. So data has to be refreshed and leave newly added fields intact.

Simon_MT · Jan 29, 2010

Your do need to use attachmewnts as you have the data in Access 2007 which will handle referentially incorporating images and the barcode can be done with a font. By all means use what you need to to get the application up and running but images and barcodes are fairly simple to implement.

Simon

Banana · Jan 29, 2010

I would like to know how Access or Firebird ODBC driver are representing those fields and whether the representation are accurate.

For some more high-level discussion on how ODBC works, see if this helps.

brixie11 · Jan 30, 2010

Firebird uses SQL same as Access, but i can not define in firebird the field as attacment.

brixie11 · Jan 30, 2010

I came across a solution and I wonder if it is possible to do this with "Docmd.RunSQL" statment, which I would have to update only linked fields. Is that possible?

Banana · Jan 30, 2010

brixie11 said:
Firebird uses SQL same as Access, but i can not define in firebird the field as attacment.

Actually my question was what did Access perceive Firebird's attachment field to be actually be?

That is, if you define a new table in Firebird with attachment data type (or whatever Firebird calls it) then you link it in Access, then when you open the table in design view, does the definition match?

Another possibility is whether Firebird can define the field as a varbinary data type or similar... will Access then pick this up and then you can use AppendChunk/GetChunk property of the DAO recordset to manipulate the binary stream.

brixie11 · Jan 30, 2010

Problem arises as I do not have access to a firebird database and I don't understand the database. I only downloaded drivers for firebird and link the tables together. So I need to update the table with the data on a linked table. I was thinking of crating two tables: one which I would copy the linked table to and other with product code and attachment fields and link product codes with two tables. When a new product code is added to a linked table I only need to add record in a table with product code and attachmets.

Then there is a problem of puting data on a form out of two tables. They are not in a drop down menu to select fields from the other table.

Banana · Jan 30, 2010

The problem is that there are several possible factors - it may be a case of Access assigning wrong data type, a case of Firebird driver not supporting the data type, a case of security permission denied to you to update this field, a case of you not actually seeing a table but only a view, and then few more possibilities.

I'd want to get in touch with whoever manages this database and work this out.

I'm a little unclear on what you mean by creating two tables. Reading your earlier posts, it sounds like you want to add attachments containing picture of the sticker to be printed to a product, which you get the data from a linked table. But I couldn't tell if you need to be able to update the linked table's data as well?

brixie11 · Jan 31, 2010

Ok I did a workaround. I created one table with only product code and attachments and created a relationship on product codes. Now only when new product is added I have to enter attachments in the table.

smig · Feb 1, 2010

If I understand you correctly there is no way for you to change the original tables, but you need to add more fields to it. is this right ?
what you want to do as a workaround is creating a one-to-one relationship with a new table that will hold the extra fields.
also there is no way for you to change the original application to add the new products to these extra fields. is this right too ?

if I'm righ you'r suggestion of running a query to add the new products to the table seems to be a nice solution.

brixie11 · Feb 2, 2010

Yes thats exactly right.

smig · Feb 2, 2010

so I think your suggestion is the best way.
you can run this Add query whenever the database is opened

Working with linked tables (1 Viewer)

brixie11

Registered User.

ghudson

Registered User.

brixie11

Registered User.

Simon_MT

Registered User.

brixie11

Registered User.

Simon_MT

Registered User.

brixie11

Registered User.

Simon_MT

Registered User.

Banana

split with a cherry atop.

brixie11

Registered User.

brixie11

Registered User.

Banana

split with a cherry atop.

brixie11

Registered User.

Banana

split with a cherry atop.

brixie11

Registered User.

smig

Registered User.

brixie11

Registered User.

smig

Registered User.

Similar threads

Users who are viewing this thread