What are your User DSN names for each computer that connects to Sage 50?

My guess is that the one your boss uses has a slightly different name than your's does.

The DSN Sage 50: Last opened. and Sage 50: "*.sai filename" are both rewritten when Sage 50 opens and possibly when Sage 50 closes (though I have never tested the close).  The username and password are never stored by Sage's programmers likely for security reasons.

My User DSN name is Sage 50: CAM_LIVE.sai while my boss uses Sage 50: CAM.sai. Although I called mine differently, they are the same "shared company". Each file is on our respective local drives. However according to what you said both should not work (store credentials) because they are Sage 50: "*.sai filename".

I resaved the shared database and renamed it exactly as the file name my boss uses. I still get the same issue. Her credentials somehow get stored while mine dissapear everytime I close out and open Sage 50 again.

The only other reason I can think of is that her Sage 50 is setup so that third parties can both Read/Write while mine is Read only. Could that be the cause?

The DSN is named based on the program name and company filename.

eg. Sage 50: CAM_LIVE.sai

This tells me when you open a file in Sage 50 you look for the file CAM_LIVE.sai to open.

When your boss opens a file in Sage 50 she looks for the file CAM.sai to open.

This is under normal circumstances.  But if you are both using the same file on your local computers, wouldn't the sai file be named the same?

However if your company file is really called CAM_LIVE.sai and both of you open that same filename, then the fact that she is using a different DSN is the reason her's are not being overwritten each time she opens the file.  Therefore, even though you said 

Marc Adjami said:

My boss has the exact same setup

it turns out you and she don't have the same setup.

The Sage 50 program creates/overwrites several DSN settings each time it opens.  If you open a file CAM.sai you will get the same settings in Sage 50: Last opened. and Sage 50 CAM.sai DSN's.  If you then rename the file to CAM_LIVE.sai, and you open the file in Sage 50, you will then see Sage 50: Last opened. and Sage 50 CAM_LIVE.sai DSN's with the same settings but Sage 50 CAM.sai could possibly have different settings but not always.

You haven't said so but if you are both using the Remote Data Access part of the program, then I would assume both *.sai files are named the same and she is just using a DSN with a different name.  Likely she is using one from when the file was just called CAM.sai and she never changed from using it with Access, while you did when the company file was renamed.

I do not believe Third-Party rights would be an issue with your disappearing credentials from the DSN.  However, I have no information what type of queries you are running on your machine.  If you are running UPDATE, DELETE or INSERT statements, then the "crashing" may be happening because you do not have the security rights at the database level to run those query statements.  You can only run SELECT statements.  I don't know if the crashing is really the program closes without any error messages or if a message shows up and the statement is just prevented from working.  If the latter, then that is by security design.

BTW, based on your mention of searching the registry, I assumed you had an IT-type background and when I put the DSN name partially in quotes, you would recognise it as a dynamic name where *.sai means any filename with the .sai extension.  I do not expect you would have a DSN name labelled that way.

You can always create your own DSN called MYFileAccess and set it up with the proper, server, port, username and password and see if it is overwritten when you open Sage 50.  It will not.  That doesn't mean it will work all the time, it just means that as long as the file is opened on the exact same port and server (localhost per your example), that DSN will always work.

Thanks for helping me out Richard! I really appreciate it. I don't understand everything you wrote. I have an IT background in the sense of building desktop computers and troubleshooting but only as an amateur. Never formal training. Ok so here is what I did, I deleted the Sage 50: CAM_Live.sai and corresponding folder. These were saved in my local drive C:/Sage. This is also where my boss stores her company file on her own computer C:/Sage/CAM.SAI.

So to eliminate factors I went back into Sage, selected "Connect to a shared company", connected to the same one my boss connects to. It prompts me to save a file. I save it just like my boss in the following path: C:/Sage/CAM.SAI. So although the file name is the same and the shared company is the same, we each have a copy of the file on our respective C drive local computer.

Another thing that I didnt mention is that when I login to sage, my user name is marc and password is my own while when my boss logs in she uses the sysadmin credentials. For the ODBC I tried using my credentials many times and even with third party access in Sage I was never able to make it work so my boss entered her sysadmin user and password in my ODBC and that worked. So while she is entering sysadmin credential in both ODBC and Sage 50 (both the same), I am entering sysadmin in ODBC and my own personal credentials in Sage 50. So you are right that we don't have the same setup.

What is Remote Data Access? I am not familiar with that. How could I find out if that is what we are using?

I tested the third party rights hypothesis and no difference as you said. The queries I was running were only select queries. Not update, delete or insert as we don't want to compromise the data in Sage 50.

You are correct, I don't have DSN anmed *.sai but as mentioned before I fiddle in registry sometimes as a amateur.

In your last step you mentioned creating my own DSN and that this would work, How would I go about doing this? Remember that when I was using my own Sage 50 credentials I was never able to achieve a successful ODBC connection. Only by using my boss' sysadmin credentials.

There are a lot of moving parts here...any way I can give you a call to discuss over the phone? I called Sage several times but nobody knows what I am talking about. I always get the really knowlegeable people here on Sage City. Thanks again.

Marc

Unfortunately, the c:\Sage folder is where your original Sage 50 installation program used to be.  Now that you have deleted the entire folder you likely deleted the C:\Sage\Sage 50 Accounting Installer Files - CDN Release 2023.0 folder as well.

Please keep track of this folder: C:\Users\Public\Documents\Sage Software\Simply Accounting\Download

It is where the original installation program is downloaded to.  After you run the update for the first time in a given year, eg. 2023.0, it creates the folder in c:\Sage to store the unpacked installation program.

When working on a local computer, I usually keep my data in c:\Sage50Data to keep it separate from everything else.

Marc Adjami said:

I went back into Sage, selected "Connect to a shared company"

= Remote Data Access therefore you are already using it.

I have never used Remote Data Access with any of my clients so I was unaware you could have the same file but different names on different computers.  It never would have crossed my mind to do that.

To set up a DSN follow the steps in the image below.

1) Open the 32-bit ODBC Data Source Administrator - use Windows search to find ODBC and pick the 32-bit version

2) Select the DSN for the settings you wish to copy.  In my example, I used Sage 50: Universl.SAI but you would use Sage 50: CAM.SAI

3) Select the Configure... button to see the settings

4) Record the settings you see, especially Server and Port.

5) Cancel out of that second window and select the Add.... button.  Enter any Data Source Name you wish but use the other settings you found in the original. eg. server localhost, Port 13540 etc.  Hit OK.

Now you should be able to use that new one. 

Marc Adjami said:

Remember that when I was using my own Sage 50 credentials I was never able to achieve a successful ODBC connection.

I haven't gone back to look but I thought in your other message thread, you said you connected but could not run a query.

Using your credentials may not allow you access to certain modules and therefore certain tables.  This would be the case if you do not have access to General or Payroll modules in Sage 50 and you are running queries on tables related to those modules.  If that is the case, your security rights are causing the problem.  If you can't run reports on the payroll in Sage 50, you can't select payroll information in Access.

Marc Adjami said:

any way I can give you a call to discuss over the phone?

You can contact me through my profile but I am a consultant (not a Sage employee) that works with this program and help clients make the most of it.  I do charge for my time when doing one-on-one consulting.  And, I can't solve your problem with Access as I have not used it with Sage 50 at all.  I have only used it with Simply Accounting prior to 2008.  I was just attempting to help you get an ODBC DSN working.

Good morning Richard,

I actually did not delete C:\Sage\Sage 50 Accounting Installer Files. I knew this folder should remain. I tried your steps to note the settings in the Sage 50: CAM.SAI configuration and to "add" a new one with my own DSN name and it worked! :) I am still using my boss' credetials but now when I close out of Sage 50 and open it again, the ODBC setup I created maintains the credentials which is exactly what I was looking for. Thank you! :)

When I said I connected, I meant that connected successfully with my boss's credentials. Never worked with my own credentials even in this case of trying to "Add" a new DSN. I checked the permissions of my boss with my own in the Sage 50 system (I took snapshots and compared side by side) and I have all the same rights under the "Rights" section and also under the "Rights With Third-Party Products". Any idea what else could prevent me from using my own credentials to setup a successful ODBC? Anyways this is not crucial as I now have a permanent working ODBC connection and that's good enough for us.

Regarding the Microsoft Access, FYI yesterday I uninstalled Office 64bit and installed Office 32bit, I got read/write permissions in Sage 50 and followed all the steps as per the resolution page you sent me. In my access DB I was running only select queries and the DB was still crashing. So it seems like 32bit or 64bit, both are unstable with Sage 50 unless there are other factors at play here... For now as a workaround, I linked (loaded) the relevant tables we need into an excel spreadsheet and used this workbook as a bridge to sync with my Microsoft Access DB. I used VBA code so that I can hit a refresh button in access and in the background it takes the excel spreadsheet, refreshes its data through ODBC and sync's it with the Access DB. Not ideal but a decent workaround to having to import table into Access every time. This way we have live data in MS Access.

I didn't know that you were a consultant but if we every need one can you send me your contact info and the rate that you charge through PM? You really helped me a lot here to better understand how it all works and I am very appreciative. Thanks again!

Marc