I'm confused (1 Viewer)

AccessBlaster

Registered User.
Local time
Yesterday, 17:21
Joined
May 22, 2010
Messages
5,828
If developer and user are same person, I don't see much reason to split - make a backup before doing any design changes.
Sometimes I will split a single user (myself) database on the network, just to take advantage of their maintenance routines.
 

deletedT

Guest
Local time
Today, 00:21
Joined
Feb 2, 2019
Messages
1,218
How much could 70 and 80 year olds know about software, seriously?
I just can't believe what I read. Why you insist on humiliating the elders.
My father at his 70s knew much more about databases that I will ever know.
 

vba_php

Forum Troll
Local time
Yesterday, 19:21
Joined
Oct 6, 2019
Messages
2,884
My father at his 70s knew much more about databases that I will ever know.
it was not meant to put anyone down. the purpose was to point out that most older people are not hackers, nor have any interest in doing that kind of work. unless they are angry people. but I guess, since healthcare is being pushed out the window in the country, maybe a lot of them are angry. I wouldn't doubt that they are.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:21
Joined
Feb 28, 2001
Messages
27,001
How much could 70 and 80 year olds know about software, seriously?

Adam, I can think of one 72-year-old person who happens to know a fair amount about Access - me.

you are telling us, richard, that a low-level user of an ACCESS database would KNOW how to hack into it?

Adam, you certainly know just enough to be dangerous. And yet you are fairly low-level based on your previous posts.

every-time the FE and BE are moved to a different directory,

Swell, that's half right. If you have a URS-style table linkage to a shared backend folder, moving the FE has no effect. Moving the BE, however, could cause disruption.
 

swell

Member
Local time
Today, 00:21
Joined
Mar 10, 2020
Messages
77
based on your forum avatar, I would've guessed this would be for seniors. ;) if it is senior citizens, I'm not even sure why you're worried about security. How much could 70 and 80 year olds know about software, seriously?
Umm well not so much the security as in deliberately wanting to hack, but there are people with enough knowledge to be dangerous in wanting to fiddle and not really knowing the consequences of the action.

This association nationally been around for over 100 years , this year my branch celebrated 80 years of formation.
There are younger members joining from time to time. Junior citizens will eventually become senior citizens.
Adam! are you going to have less knowledge when you are my age? :unsure:
 

vba_php

Forum Troll
Local time
Yesterday, 19:21
Joined
Oct 6, 2019
Messages
2,884
Adam! are you going to have less knowledge when you are my age? :unsure:
according to most here, I have no knowledge whatsoever, so your point is moot. :p by the way, congrats on the expansion of your org. that's a good thing.
 

swell

Member
Local time
Today, 00:21
Joined
Mar 10, 2020
Messages
77
Is this a frequent occurrence? I would have thought the be location would be somewhat permanent.
Well at the moment it is permanent in that it was my job as Vice President to maintain these records, this year the district branch decided to have "Database system" written in "YUCK" excel, which is great for what it was designed for, so many people with a little knowledge think its a database :(.

I am not sure who designed it, but I could see it was easy to make many mistakes just entering the data and also ability to fiddle the data, hence me deciding to do it ms-access.

I recently resigned my position and will need to teach one or more members how to maintain this system.
Who ever takes over the job will be running it on their own PC at home I would suspect - like I do.
And I could teach the next person how to manage the table links, but what of the future?

The other thing in the back of my mind is once the other sub-branches find out about my little database they may want a copy which would need modification for every sub-branch the way it is sub branch name on the reports and forms.

It would be nice if the state body could/would allow room on their server (I am only assuming they have one) to store the BE but if that happens in the future who knows?
 

swell

Member
Local time
Today, 00:21
Joined
Mar 10, 2020
Messages
77
according to most here, I have no knowledge whatsoever, so your point is moot. :p by the way, congrats on the expansion of your org. that's a good thing.
Thanks Adam, I am not sure about expansion though, we battle to maintain our little sub branch numbers but we punch above our level :)
 

swell

Member
Local time
Today, 00:21
Joined
Mar 10, 2020
Messages
77
Swell, that's half right. If you have a URS-style table linkage to a shared backend folder, moving the FE has no effect. Moving the BE, however, could cause disruption.
Yes! that's my concern.

If you download any of my Templates the master would be best each has a link manager that pops up if a backend can't be found I've used it for nearly 20 years

Keep safe mick
Mick I will investigate your templates and see if I can implement a solution that I can handle :)

Regards
Bill
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:21
Joined
Sep 21, 2011
Messages
14,048
The way I would get a round this is have a folder called whatever you want, for this example I will call it DB.
This folder should be on every PC that will use this database.

Then pass the BE & FE to the user witn the instructions that both files should be in this folder before being used.?
Give them the shortcut as well that points to this folder and database.

Job done.?
 

vba_php

Forum Troll
Local time
Yesterday, 19:21
Joined
Oct 6, 2019
Messages
2,884
Thanks Adam, I am not sure about expansion though, we battle to maintain our little sub branch numbers but we punch above our level :)
oh no problem. I want to say too, that I still get a kick out that "old man with the beard" avatar you've got on this board. LOL. what a giveaway....
 

sxschech

Registered User.
Local time
Yesterday, 17:21
Joined
Mar 2, 2010
Messages
791
The way I would get a round this is have a folder called whatever you want, for this example I will call it DB.
This folder should be on every PC that will use this database.

Then pass the BE & FE to the user witn the instructions that both files should be in this folder before being used.?
Give them the shortcut as well that points to this folder and database.

Job done.?

Another option if the FE and BE are going to reside in the same folder then instead of having a specific folder, is to have code which tells the relinker to link to the BE in the same path as the FE. Then it wouldn't matter what folder or computer it was saved to*. Something like this:

Code:
Call LinkTables(FilePath(CurrentDb.Name) & "BackendDB.accdb")

If you need further code, I can provide.

*Also if various computers will use the program need to be sure that the Trust Center Settings have the folder in its list of trusted locations
 

swell

Member
Local time
Today, 00:21
Joined
Mar 10, 2020
Messages
77
Another option if the FE and BE are going to reside in the same folder then instead of having a specific folder, is to have code which tells the relinker to link to the BE in the same path as the FE. Then it wouldn't matter what folder or computer it was saved to*. Something like this:

Code:
Call LinkTables(FilePath(CurrentDb.Name) & "BackendDB.accdb")

If you need further code, I can provide.

*Also if various computers will use the program need to be sure that the Trust Center Settings have the folder in its list of trusted locations
I haven't had a chance to investigate prior suggestions but this looks like an elegant solution that might work to automatically solve the problem.
If you could supply further code I would appreciate.
I am assuming this would go in "on Load" of my main form?
 

swell

Member
Local time
Today, 00:21
Joined
Mar 10, 2020
Messages
77
oh no problem. I want to say too, that I still get a kick out that "old man with the beard" avatar you've got on this board. LOL. what a giveaway....
:giggle: Lifes too short not to be able to laugh at yourself. In my mind I still look a lot like your avatar ;).
 

vba_php

Forum Troll
Local time
Yesterday, 19:21
Joined
Oct 6, 2019
Messages
2,884
:giggle: Lifes too short not to be able to laugh at yourself. In my mind I still look a lot like your avatar ;).
well truth be told that picture was taken a few years ago so I might look just a tad bit different but it's pretty much the same
 

sxschech

Registered User.
Local time
Yesterday, 17:21
Joined
Mar 2, 2010
Messages
791
I'll post some of the code elements tomorrow when I'm back in the office.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:21
Joined
Oct 29, 2018
Messages
21,358
I haven't had a chance to investigate prior suggestions but this looks like an elegant solution that might work to automatically solve the problem.
If you could supply further code I would appreciate.
I am assuming this would go in "on Load" of my main form?
Just in case this helps...

 

sxschech

Registered User.
Local time
Yesterday, 17:21
Joined
Mar 2, 2010
Messages
791
Here is the code for linking. If you need more information or missed providing a code block, let me know. Please try this on a copy of your database in case it needs further adjustment to work for your situation. This code was done some time ago and unfortunately, I did not put source credits, so don't recall exactly where I pulled it from. Sorry about that.

Put this in the open event of your form and change BackEndDb to the name of your Backend file (name portion only as the path is taken care of by FilePath)
Code:
Call LinkTables(FilePath(CurrentDb.Name) & "BackEndDb.accdb")

Put this in a Standard Module. NOTE-Change BackEndDb in this code to your Backend file:
Code:
Function LinkTables(dbPath As String) As Boolean
'This links to all the tables that reside in DbPath,
'  whether or not they already reside in this database.
'This works when linking to an Access .mdb file, not to ODBC.
'This keeps the same table name on the front end as on the back end.
    Dim rs As Recordset
    Dim stsql As String
    
    stsql = "SELECT Name " & _
            "FROM MSysObjects IN '" & dbPath & "' " & _
            "WHERE Type=1 AND Flags=0"
    On Error Resume Next

'get tables in back end database
    If InStr(dbPath, "BackEndDb.accdb") > 0 Then
        stsql = stsql & " AND left(Name,6) <> 'mSPLIT'"
    End If
    Set rs = CurrentDb.OpenRecordset(stsql)
    If Err <> 0 Then Exit Function

'link the tables
    While Not rs.EOF
        'delete old link, assuming front and back end table have the same name
        DoCmd.DeleteObject acTable, rs!Name
        'make new link
        DoCmd.TransferDatabase acLink, "Microsoft Access", dbPath, acTable, rs!Name, rs!Name
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

    LinkTables = True
End Function

Put this code in a standard module:

Code:
Function FileNameNoExt(strPath As String) As String
 'https://sqlaccxl.wordpress.com/2013/03/06/vba-function-to-extract-file-name-withwithout-file-extension-or-path-only/
    Dim strTemp As String
    strTemp = Mid$(strPath, InStrRev(strPath, "\") + 1)
    FileNameNoExt = Left$(strTemp, InStrRev(strTemp, ".") - 1)
End Function
 
'The following function returns the filename with the extension from the file's full path:
Function FileNameWithExt(strPath As String) As String
    FileNameWithExt = Mid$(strPath, InStrRev(strPath, "\") + 1)
End Function
 
'the following function will get the path only (i.e. the folder) from the file's ful path:
Function FilePath(strPath As String) As String
    FilePath = Left$(strPath, InStrRev(strPath, "\"))
End Function
 

Users who are viewing this thread

Top Bottom