Results 1 to 14 of 14

Thread: upsizing wizard problems

  1. #1
    Join Date
    Dec 2008
    Posts
    27

    upsizing wizard problems

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Login to where? To sql server? You have to create sql login in sql server if so.

  3. #3
    Join Date
    Dec 2008
    Posts
    27
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You still didn't tell login to what.

  5. #5
    Join Date
    Dec 2008
    Posts
    27
    "..had a login form popup when open the db, then user could login based on usertable..."

    when they login to the database

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  7. #7
    Join Date
    Dec 2008
    Posts
    27
    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.
    Attached Images Attached Images

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Logon to which db? Access db or sql db?

  9. #9
    Join Date
    Dec 2008
    Posts
    27
    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,

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Then you have to ensure you created sql login for those windows id. Not just add user to table, sql doesn't authenticate user based on your table.

  11. #11
    Join Date
    Dec 2008
    Posts
    27
    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.

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Possible to get rid of _local tables?

  13. #13
    Join Date
    Dec 2008
    Posts
    27
    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.

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •