updating and distributing Access 2016 FE

Microsoft Office 2010 professional (full...
February 7, 2017 at 13:42:07
Specs: Windows 7
Greetings,

I’m tasked to support an access 2016 db that was created by someone else. The DB is split:
the BE is housed on department shared drive and users have copies of the FE that is linked to the BE.

Couple of questions:
1. I made a copy of an existing form (frmLogin) named it (frmLogin_new) and made some modifications: added a logo, align labels etc. how do I replace the old form with the new one and distribute the FE to end users?

2. There is a report that managers attempted to run (status report: open/closed etc. when they click on open cases button, it will prompt for a name they enter the requested question nothing happens. However, when I tried the same report I could view the report without any problem. Can this be a permission issue?


TIA,


See More: updating and distributing Access 2016 FE

Report •

#1
February 7, 2017 at 14:00:06
Just FYI...

AFAIK, there is little to no Access expertise in this forum. Unless we have some new members that I have not "met" yet, you may want look elsewhere for your answers.

I offer that information just to save you some time.

Got an Excel question? Now that we can help with. Word and PowerPoint also, but Access...not so much.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 7, 2017 at 16:55:19
Thank you for the suggestion. I’ll keep this in mind.

Regards,


Report •

#3
February 8, 2017 at 03:56:28
Are the users linking to the form, or do they actually have it stored locally? Storing it locally would not necessarily update the information on the database.

As for the report, check the query behind it, (I'm guessing it's an input query,) and make sure the information is correct as far as the table information from where the data is being pulled.


Report •

Related Solutions

#4
February 8, 2017 at 04:15:43
When I build my databases I also split the FE and BE, however rather than giving each user a COPY of the FE I send them a link to it, this way when I make changes to the FE it will be reflected on their side when they click on the link.

When making changes/updates I set the 'Display Form' under "Options > current Database' to a maintenance form, telling the users that updates are being made and to expect some changes.

In your case I would probably look into sending out a link, you can force the users to use the new link rather than their old FE by either changing the BE password.

From here on then if you make changes the users will always get the latest updates as they will be accessing the FE via a link.

that's the way to go.


As far as the report goes, when you run the report do you also get prompted for an input? if not then it could possibly be that your query has an error. If you are prompted for an input then perhaps there are permissions set for users in your DB? I tend to do this also, each user has a read only, read write, view reports, run reports etc permissions. However without knowing how your DB is setup its difficult to tell.

if you have a users table, try changing your userID with anothers and see what happens, if there are no permissions set then check the query and also check what the users are entering when the prompt somes up.

keep us posted.


Report •

#5
February 8, 2017 at 05:46:39
OK...I stand corrected. ;-)

beachyhbt and AWTL:

Sorry guys and thanks for stepping up.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 8, 2017 at 09:00:52
Thank you both for the useful suggestions.

1. I inquired about FE deployment in the past. I was told everyone works on the same program, and when updates were done they were first done in the test to see if they worked. Then they were incorporated into the FE.as an exclusive user.


2. The query is ok as I’m able to run the report & get correct result. Also, another manager can run the same report without any problem. However, there is a new manager on board that is unable to run the report which makes me to assume a ‘permission issue”. I’ll look into how permission was set up as suggested by (AlwaysWillingToLearn)

How is access 2016 permission differ from the older version? e.g. Access 2000 etc.?

Regards,

message edited by ocm1


Report •

#7
February 9, 2017 at 01:14:36
Derby,

No worries, I haven't really responded to Access questions before.

Ocm 1,

they were incorporated into the FE.as an exclusive user.

although I understand the concept of exclusive user, do you mean that you would have to update each FE for each user individually, or do you update one FE and send the users a new copy of it?

This approach doesn't sound very robust or controlled, unless of course you have a means of preventing an older version from working once a new one is available? (many ways of achieving this)

As I described above the best approach would be to send out a link to one FE.

In terms of testing, I don't know how critical your activities are but when we run tests on our databases, we make the changes and get the users to use the system for a few ours to see how it functions, they are the best at using the system and would probably be able to test all features and functions between them. If something fails all we do is reinstate the original backup FE. The BE should in theory still have all the changes they have made unless of course the FE updates were really wrong.

In terms of permissions, what I was talking about was permissions that you build as a Access developer, for example, I have a table in all my databases called TblUserPermissions. Within this I will have the user id, users access level and user status.

In another table I will then build which features of a form are enabled disabled based on the user access level.

This is then loaded at run time and features are enabled disabled accordingly. Same with reports, my table will define which user is able to view or run a certain report, so I am thinking perhaps someone has setup a similar feature in your database? its a very common way of setting permissions, I don't know of any way to natively set permissions like this in Access. So my guess is that there is something in a table somewhere.


Report •

#8
February 10, 2017 at 09:00:11
Thank you for the reply.

When I took over the position, the person who designed the db wasn’t around. I’ve yet to figure out how everything is set up etc.

In terms of permissions, I’ve couple of managers log into my PC using their own credentials and run the same report that they were unable to run from their PC.
I was surprise to see they were able to run the report without any problem.

Can this be a network permission issue somehow attached to computer name? How/where do I check this?

TIA,

message edited by ocm1


Report •

#9
February 10, 2017 at 09:15:14
This could be an ODBC issue or a dll, ocx issue. The only way to figure it out is to play around. I dont think there are network permissions and to be honest to implement such security wouldnt be hard but would be pretty pointless, maybe.

So id be more imclined to lool at their machines and configurations,

do they have the same ODBC if any as you do? Are they definitely linking to the correct BE file?
Are all the same references refernced?

So many things to check i am sure if you hunt around and prehaps spent a few hours at their computer you would nail it.

We are always here to help but i think for us to be able to you need to investigate and come back with some results.

EDIT: one thing i should mention actually regarding permissions, you can infect set permissions for a machine if you know its asset number and that asset number is stores in a table somewhere, have a look around for this too.

Good luck

message edited by AlwaysWillingToLearn


Report •

#10
February 10, 2017 at 10:08:23
re: "Can this be a network permission issue somehow attached to computer name? How/where do I check this?"

Does your company have a IT department that is responsible for setting up user accounts, granting server/network access, etc. Perhaps someone from that department could look at the user and/or machine profiles and see why it works from your machine but not from the other users' machines.

re: "When I took over the position, the person who designed the db wasn’t around."

Did you actually expect management to make it easy for you? ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
February 14, 2017 at 12:56:34
Thank you all for your input.

AlwaysWillingToLearn,

re: This could be an ODBC issue or a dll, ocx issue…
How do I go about checking the above?

I’m not sure if the following test I performed give us any lead the cause(s) of this issue:

1. I can run the report fine.

2. Few managers are unable to run the report: But, when I’ve them come to my desk, have them login using their credentials (ms access credentials) on my PC, the report runs fine for them.

3. I used my ms access credentials to log into one of the manager’s PC, I was unable to run the report.

Though not sure, one of the manager thinks the issue started after ms office was upgraded to office 2016.

TIA

Regards

message edited by ocm1


Report •

#12
February 15, 2017 at 00:08:05
Hi ocm1,

ok so from your test I would conclude that the problem is machine based, seeing as everyone can run the report on your machine, but you were unable to do the same on theirs.

a couple of questions, do you all have full versions of MS Access installed or do you, as the administrator have the fill version and everyone else runtime?

hoping they have the runtime environment installed, the easiest solution would be to install the latest version of MS Access runtime.

If you all have full versions installed then this could get a little complicated, without being at the machines to compare the various references. Here is what you need to do to compare referneces, give it a go and make sure you check them word for word

For example a reference to MS Excel could be:

Machine 1
- Microsoft Excel 14.0 Object Library

Machine 2
- Microsoft Excel 15.0 Object Library

Ok, so open your database, go into to VBE by clicking on 'Database tools' in the ribbon then 'Visual Basics' from there click on 'Tools' then references.

What you need to ensure is ALL references on each machine are the EXACT Same as on your machine. As I pointed out above sometimes there is a version issue which can cause issues.

The other thing to look out for is the word 'MISSING' proceeding any reference.

NOTE: only the references that are ticked/checked are the ones that are being applied to your database so ignore everything else. But do ensure that the same references are ticked on all databases.

is anything that is ticked has the word MISSING in front of it, then it needs to be fixed, this could be that the file is missing or unregistered on that machine.

Check these things are let us know what you find.



Report •

#13
February 15, 2017 at 10:13:44
AlwaysWillingToLearn

Thanks for the reply post.

I checked the users machine and determined:

1. They are running Access 2016 (but couldn’t tell if it’s runtime or full version)
2. I checked the reference file and determined to be the same as mine.

The following are checked in all users/mine as well. I didn’t see anything ‘missing’

Visual Basic for Applications
Microsoft Access 16.0 Object library
OLE Automation
Microsoft Office 16.0 Access database engine Object Library

TIA,

Regards


Report •

#14
February 15, 2017 at 11:36:13
ocm1, in an earlier post you said:

1. I can run the report fine.

2. Few managers are unable to run the report: But, when I’ve them come to my desk, have them login using their credentials (ms access credentials) on my PC, the report runs fine for them.

3. I used my ms access credentials to log into one of the manager’s PC, I was unable to run the report.

I don't use Access so maybe this question doesn't make sense, but I'll toss it out anyway:

You used some very specific words there: ms access credentials. Are these credentials different than a user's Windows credentials? i.e. When they are at their own machine, logged into Windows, do they then log into Access with a different set of credentials? When they came to your machine, were you logged into Windows under your credentials and then they logged into Access using their Access credentials but under your Windows credentials? (I'm getting dizzy!)

Example, we run ACT! as our calendar and contact management application. Once I am logged into my Windows machine and start ACT!, I have to enter my ACT! user name and password in order to use the remote database. i.e. it's a separate set of credentials.

In other words, could there be something to related to the Windows user profile that allows them to run the reports on your machine under your Windows profile with their Access credentials but prevents you from running the reports with your Access credentials using their Windows profile?

In other, other words, have you tried logging into their machine under your own Windows profile and Access credentials to see what happens?

OK, so here is a really out-of-the-box suggestion. I'm just tossing it out to keep in your back pocket if everything else fails. Granted, it might sound strange, but if nothing else works, it can't hurt.

Can you either move one of their machines to your network drop or run a network cable from your office to one of theirs? How weird would it be to find out that you are on a different subnet/router/switch and there is some network issue causing the problem?

As I said, out-of-the-box, but if everything else is the same (machine hardware, user profiles, Access configuration, etc.) then maybe it's something in the network environment.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
February 15, 2017 at 13:43:48
Thank you for the reply

re: Are these credentials different than a user's Windows credentials? Yes, they are different.

re:…could there be something to related to the Windows user profile that allows them to run the reports on your machine under your Windows profile with their Access credentials but prevents you from running the reports with your Access credentials using their Windows profile?

That’s what I’m thinking: the issue may have something to do with their machine. i.e. user profile/computer name etc.

As to moving one of their machines to my network drop etc. I cannot invasion as the managers are very busy and always work on a deadline. Besides, they are located in a different floor than I’m.

Regards,


Report •

#16
February 15, 2017 at 17:29:08
re: "That’s what I’m thinking: the issue may have something to do with their machine. i.e. user profile/computer name etc."

So, are you going to try my suggestion, I.e. Log into Windows as yourself on one of their machines and then see if you can run the report? If you can, then it's a Windows profile thing.

Is there a person at your organization responsible for the Windows environment?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#17
February 16, 2017 at 08:46:28
I did log into windows as myself onto end-user’s machine, but I was unable to run the report. I wonder if this is somehow attached to the “computer name” I’ll reach out to the IT folks to get their feedback.

TIA

Regards,


Report •

#18
February 16, 2017 at 10:21:40
Well, we're ticking 'em off one by one. ;-)

Do let us know once you find the root cause.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
March 2, 2017 at 01:34:29
How are you getting on with this?

Report •

#20
March 2, 2017 at 13:27:55
AlwaysWillingToLearn,

Thanks for the followup on this. IT for some other reasons is going to upgrade everyone's PC. I plan to give them a clean FE, as they may have a corrupted FE. I'll post back with my findings.

By the way,
1. Is there a way to find out if end-users are running a full or run time version of access?

2. Currently, the FE is in .accdb format. Is it best to convert it to accde format before deploying it to end-users?

TIA,



Report •

#21
March 6, 2017 at 07:08:05
Hi,

Yes there is a way to determine if a user is running Runtime or Full version. You will
need to have a login table, or a file to where access can output the results.

When a user logs into one of my databases it will automatically save, userid,
time and date and version

You can use the following code to achieve this.

Function RecordLogin() As Integer
    'Record a user's logging in event in the logins table
    
    Dim db As Database
    Dim strVer As String
    
    If SysCmd(acSysCmdRuntime) = False Then
    strVer = "Full"
    Else
    strVer = "Runtime"
    End If
    
    Set db = CurrentDb
    
    Dim sql$
    sql = "INSERT INTO tblLogin (strUser, strMachine, dteLogin, strJetVer) VALUES ('" & Environ("UserName") & "' , 'PC', #" & Format(Now(), "dd-mmm-yyyy hh:nn:ss") & "#, '" & strVer & "')"
    
    db.Execute sql, dbFailOnError
End Function

All my databases are saved as .accdb iv had no problem with this, also if any
anomalies arise on any users machines, I can open up VBE (if they have full
version of access) and debug the code at their machine, not sure if .accde would
allow this.


Report •

#22
March 6, 2017 at 09:50:34
AllwaysWillingToLearn,

While searching online someone suggested the following.

SysCmd(acSysCmdRuntime) will return True if it's the runtime, and False otherwise.

I’m not sure what I was doing wrong, it didn’t work for me.

Thank you for providing helpful method (code).

Regards,


Report •

#23
March 6, 2017 at 12:59:24
Hi,

I think i recall that thread from a while back, i thinkcthe problem the OP had was that he was using the runtime switch in his shortcut which was forcing the db to open as runtime.

You will have to modify the code i gave you to fit your database structure: the best way to test the code is to create a routine, not the function as suggested above.

Copy everything from

Dim db as database

To

End if

Then add debug.print strver

You will need to have the immediate window open in order to see the result. Alternatively change debug.print to msbox

Iv used this so many times now and can confirm that it absolutely does work.

Probably work making a new small db that checks this and run it on all machines. Might be worth adding each users username, pc name and access version to a table this will tell you which user is running what version.

Dont give up we will crack this just keep plugging away.


Report •

#24
March 7, 2017 at 00:36:58
This will return the version for you in the immediate window and to a message box

Public Sub GetVersion()
    'Record a user's logging in event in the logins table
    
    Dim db As Database
    Dim strVer As String
    
    If SysCmd(acSysCmdRuntime) = False Then
    strVer = "Full"
    Else
    strVer = "Runtime"
    End If
    
    Debug.Print strVer
    MsgBox strVer
  
End Sub


Report •

#25
March 7, 2017 at 07:58:39
AWTL:

May I pick a nit? Perhaps my "nit" doesn't even exist.

Since I know nothing about Access, I'll ask this question right upfront:

Are Full and Runtime the only 2 possible answers to the question on the table? Could there be something like a Developers version or anything other than Full or Runtime?

Here's my nit: If SysCmd(acSysCmdRuntime) = False, then your code assumes the version is Full, when in reality all we know is that it is not Runtime.

Again, if Full and Runtime are the only possible versions that could be installed, then Full is the correct answer for False. If any other version could be installed then we don't actually know that it's Full, just that it's not Runtime.

If my nit doesn't exist, then I will quietly crawl back in the shadows and leave you experts to continue hashing this out. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#26
March 7, 2017 at 08:39:16
Hi Derby,
As far as my experience goes there can only be a full version or a runtime version. The full version allows a developer to open the database for updates and changes by holding down the shift key whilst opening. Therefore no requirement for a third version.

I have always only seen full or runtime though i could be wrong but in 12 years iv not seen any other type.

This code is either true or false for that very reason. I remeber back in the good days when i used to code in vb6, to obtain the operating system version i had a select case statement which had multiple options for every version of Windows, that sort of function does not exist as far as i am aware to obtain the above.

Good point though! Will look into it to ensure i am not mistaken.


Report •

#27
March 7, 2017 at 13:06:30
AllwaysWillingToLearn

Many thanks AllwaysWillingToLearn

Then code you provided worked beautifully!

Regards,


Report •

Ask Question