I was working with Allan over in the access forum and created a DB based on one of his designs. got it mostly like I wanted and then thought I would move it to sql. I ran the upsize wizard (following a MS bulletin on using it). Seemed to go ok except that now if I add a user to my user table they show up but can't log in. and in the user screen they can not add any records.
I know it will be hard to help since you don't have the db to look at, but let me know what I can do to help. i'd really like to make this work on sql
I had made a users table in access, had a login form popup when open the db, then user could login based on usertable.
user table exists on sql, and I can add names to the table, but only the ones that were in when I upsized it are able to login. and even those cant change any information on their form which pulls from a table
yes they do, I turned them off and then back on still same thing..
I have played around with it and think I have discovered where part of the problem is but I have not idea how to fix it.
I have a frm_main which asks for user name and password, based on user table, then I have a frm_utility which kind of caches the user name for use on the other forms. if I open frm_utility it shows the user I logged in as, but when I open the data entry form it does not retrieve it. i guess something in the code from access doesn't work in sql.
the other thing I noticed is, it has 2 tables for everything, 1 linked and then one labeled _local if I add a user through the add user form it updates the one on sql, but not the local. I'm just guessing here but I think that's why new users can't login, for some reason it's trying to verify though _local table. nothing in the module calls to _local though.
RMIAO i guess I don't understand what you are asking. maybe I wasn't clear.
I had an access database when you opened it a form opened automatically and asked you to login to the database, not so much for security but to track who made what changes and so each user would only be able to see their records. I had it working, and thought I would move the db to sql server. The tables moved fine. when I open my add users form it works and adds the user info to my users table. but when I open access with the tables linked to sql and the login form pops up and they try to login it will not let them says user not found,which is an error message i have in the module if the user is not in the table.
I guess it would be logging into sql db since I migrated it to sql.
everyone authenticates to the sql server via their windows id,
i thought i just said that
"..everyone authenticates to the sql server via their windows id, .."
they can use other sql db just fine.
----------------------
ok it does have something to do with the dual tables.
if I add a user (with my add user form) it updates the sql table, the user cannot log in. If I add the user to the _local table they can login, now here's a twist i have a section in the module that checks for expired passwords, this section correctly reads from the sql table. If I expire the pswd on the local table it has no effect, but if I expire it on the sql table they get the expired pswd form.
well that's one thing I was hoping someone here could tell me. They weren't there until I upsized. This is the first time I have ever tried to upsize, I didn't know of they were needed for some reason.
Don't think upsizing will generate _local tables, at least I didn't get those when I upsized access to sql. But do need change code in access to point all processes to sql.