Changes to BE table won't be represented on FE (1 Viewer)

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
I have some code to change the structure of the BE tables (When I need)
It work perfect.
BUT for one particular table it won't be represented on the FE
If I look at the BE the table is OK, but looks different in the FE (Table structure). Data is OK on FE too.
Tried to delete, compact the FE and relink, with no help :banghead::banghead:
 

June7

AWF VIP
Local time
Yesterday, 17:07
Joined
Mar 9, 2014
Messages
5,470
Why do you need to implement code that routinely alters db structure?

Don't really know what you mean by "won't be represented" nor "looks different". Maybe provide images or the database, front and back ends.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:07
Joined
May 7, 2009
Messages
19,237
have you tried using the TransferDatabase() method:
Code:
DoCmd.TransferDatabase TransferType:=acLink, DatabaseType:="Microsoft Access", DatabaseName:="your BE Path and fileName", ObjectType:=acTable, Source:="table Name to import"
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
have you tried using the TransferDatabase() method:
Code:
DoCmd.TransferDatabase TransferType:=acLink, DatabaseType:="Microsoft Access", DatabaseName:="your BE Path and fileName", ObjectType:=acTable, Source:="table Name to import"

Thanks, I will try
In what does it make any different then normal linking ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:07
Joined
May 7, 2009
Messages
19,237
no difference.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:07
Joined
Feb 28, 2001
Messages
27,179
Note that actually altering structural factors on a live database is generally considered a total no-no and has been that way for every database I've ever seen, from the simplest Access back-end all the way up to a U.S. Navy dedicated ORACLE Enterprise Server-based system with a many-gigabyte, many-disk "footprint."

If you are altering CONTENT while live, that is what databases do. But altering STRUCTURE on a live system LOCKS the schema, locks everyone using that schema, and you run a SERIOUS risk of data corruption if someone was in a table based on the part of the schema you are altering. Not to mention that the changes aren't truly permanent until everyone has exited and, for Access, maybe not even until after a compact & repair has been run.

I just about don't care what your strategy is, and don't care about your business model. An on-the-fly structural change where users are potentially live at the time is just a bad idea. The ONE AND ONLY time this type of thing will work is if YOU are the only person in the DB at the time (and can prove it). So if this is a standalone database you are using for yourself, go for it and I apologize if I came on a bit strong. Otherwise, rethink what you are doing.

June was somewhat negative on the "dynamic structural changes" idea. I am a bit more than "somewhat negative." Like June, I am confused about the infrastructure that does not include all of the BE tables linked to the FE. What's up with that?
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
Note that actually altering structural factors on a live database is generally considered a total no-no and has been that way for every database I've ever seen, from the simplest Access back-end all the way up to a U.S. Navy dedicated ORACLE Enterprise Server-based system with a many-gigabyte, many-disk "footprint."

If you are altering CONTENT while live, that is what databases do. But altering STRUCTURE on a live system LOCKS the schema, locks everyone using that schema, and you run a SERIOUS risk of data corruption if someone was in a table based on the part of the schema you are altering. Not to mention that the changes aren't truly permanent until everyone has exited and, for Access, maybe not even until after a compact & repair has been run.

I just about don't care what your strategy is, and don't care about your business model. An on-the-fly structural change where users are potentially live at the time is just a bad idea. The ONE AND ONLY time this type of thing will work is if YOU are the only person in the DB at the time (and can prove it). So if this is a standalone database you are using for yourself, go for it and I apologize if I came on a bit strong. Otherwise, rethink what you are doing.

June was somewhat negative on the "dynamic structural changes" idea. I am a bit more than "somewhat negative." Like June, I am confused about the infrastructure that does not include all of the BE tables linked to the FE. What's up with that?

I do aware that this is a bad idea, and a big no-no.
But this is an old running application, and as life develop apps develop too.
If you don't keep developing your App, as life go on, it's a dead one.
A simplest Access back-end and U.S. Navy dedicated ORACLE Enterprise Server-based system can be closed for any use even for days, if required, and make the changes. but this can't be done in many small business all over the place.
I do it only when upgrading the FE db.
I do make a backup for the BE db before touching it, and so far I had no problem.

The only problem I have now is that for some weird reason the linked table looks different (Table structure-Field Format, Description, Field Size ...etc) then the real one in the BE :eek:
This is only for one table :eek:
Tried to remove the table from FE, Compact and link again, with no help :banghead:
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
I attached the same table both in the BE and the linked one in the FE
The table in the BE is correct.

If I change the ordinal position of fields it do show in the FE

BE Table.JPG

FE Table.JPG

Most of you probably won't understand the Hebrew, but you sure can see it looks difference.
You can see the Description is missing and also the specific selected field structure looks different.
 

June7

AWF VIP
Local time
Yesterday, 17:07
Joined
Mar 9, 2014
Messages
5,470
Yes, can see differences. No idea why. Try creating a new table in the backend with the same structure and data. Link it and see if that corrects issue.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:07
Joined
Feb 28, 2001
Messages
27,179
can be closed for any use even for days, if required, and make the changes.

Actually, no, they can't. You have to schedule upgrades literally weeks in advance and get permission from people at Admiral rank and not always just a two-star. But we've beaten that horse so I won't flog it any more. You know it is bad. You know there is a risk. So let's try to figure out your problem.

In your dialog with Arnel, he suggested using a method that would directly build a link from the FE to the BE. You had mentioned a routine that you used to make structural changes in the BE. What we need to do is figure out when this difference slips in. You say you have done a sequence of Delete FE Table link; Compact & Repair (C&R); Create FE table link - and that sequence causes a difference. So specifically when you do this, how do you create the FE link after the C & R operation? Your code, a ribbon manual operation, or code like Arnel suggests? OR was your code more or the less the same as what Arnel suggested?

The reason I'm asking the question this way is that deletion of a link followed by C&R should leave no vestigial traces of any previous incarnation of the deleted link. That is because of the way a C&R works. What you have after that step isn't even the same file. It is a structural copy of the currently active content of its predecessor file, omitting all obsolete, deleted, and inactive content. But it's not the same file because during the C&R, both the original and the new files coexist.

Therefore, if this happens, it cannot be due to any content in the new version of the file; it has to originate with something inside the old file that stays visible and active even after the link removal.

I also want to avoid making an assumption. When you say that you have tried to delete the link, C&R, and rebuild the link, that obviously is on the FE file. But have you separately tried to do a C&R on the BE file? Because the link HAS to use data from the BE file, so I'm wondering if looking at the FE file is looking in the wrong place.
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
Maybe I was not clear enough.
I tried to delete the link to the specific table, C&R and relink because it looked wrong on the FE.

On my test machine I do not need to relink.

I mostly don't understand why is it happening with this particular table. maybe it happen with few more and I did not notice.

It sure does not looking at wrong place. Data in file is OK. Also if I change the ordinal position it will be reflected correctly at FE, but not the FieldFormat, FieldDefaultValue..etc

When i deploy the FE to the end user he point to the location of the BE file (normaly it wont be placed at the same exact place as on my develop machine :D), make all the required changes to the BE, and use code to relink.
Maybe the problem won't even exist on the end machine.

Is there a way to C&R the BE, from the FE?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:07
Joined
May 7, 2009
Messages
19,237
so you are deploying the Fe to users?
if so, relink using hard link.
use your UNC or yiur Static IPnumber and the path of the be.
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
so you are deploying the Fe to users?
if so, relink using hard link.
use your UNC or yiur Static IPnumber and the path of the be.

Each user hold it's oun BE.
If I had direct access to the BE I could do it on my side, no need for any code to change table's structure.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:07
Joined
Feb 19, 2013
Messages
16,610
Correct me if I am wrong but from what I have seen of the uploads, the amended table structure is showing correctly, what is not is the field notes.

If so, why does this matter?

I wonder if this issue is connected to the one you can get with the case of field names. Sometimes if you change something from 'myfield' to 'myField' the change in case won't hold. I can't remember why per se but think it was due to the way the field name was stored.

I also wonder if this is something to do with your 'table properties' table - subject of another thread.
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
Correct me if I am wrong but from what I have seen of the uploads, the amended table structure is showing correctly, what is not is the field notes.

If so, why does this matter?

I wonder if this issue is connected to the one you can get with the case of field names. Sometimes if you change something from 'myfield' to 'myField' the change in case won't hold. I can't remember why per se but think it was due to the way the field name was stored.

I also wonder if this is something to do with your 'table properties' table - subject of another thread.

If you look carefully you will notice that for some YesNo fields (look at the selected one) it will not reflect correctly the Fornat, DefaultValue and will show them as TextBox rather then Combo.

Does it matter? Not realy, as users wont work directly on tables.

The Table's-Structure-Table you mentioned (In my other thred) is sure used here.
First I collect all the info and store it (this I do when I develop only) and later I use it to change what I want, by changing into this table.
This table store the info as it reflects in the FE. This is how I noticed changes I made are not saved (after several tries I opened the BE too to look into)
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
Thanks for all the help here
Even if it won't solve the issue I have (which is not a big one) I will take some points from this discussion :BigHug:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:07
Joined
Feb 28, 2001
Messages
27,179
Each user hold it's own BE.

Then other than the design of the FE, what are they sharing? This is a VERY confusing situation and is very hard for my to wrap my head around it.
 

smig

Registered User.
Local time
Today, 04:07
Joined
Nov 25, 2009
Messages
2,209
Then other than the design of the FE, what are they sharing? This is a VERY confusing situation and is very hard for my to wrap my head around it.
What I mean is that I don't have access to the users' BackEnds
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:07
Joined
Feb 19, 2013
Messages
16,610
@smig. Sounds like you are either supplying software to clients and wanting to do updates by sending them a new front end, or your company has people out in the field who cannot access a server and perhaps communicate by emailing files?
 

Users who are viewing this thread

Top Bottom