Give the user local administrator rights.

She already has them - and can access the other 4 company databases, plus the play databases.  She cannot access this one database, as well as the corresponding play database.

There's no reason to use scripts to set anything up.  Database setup should use the SQL server name for _all_ companies.

Hi Jay

We use a script to create the ODBC connections as we have 5 company databases, 5 play databases and the system databases - so there are 11 to set up.

Are there any suggestions for the issue I am having? Invalid Database Specification - for one user on a new computer for 2 databases only (one live and the corresponding play one)

Thanks

Anna

1. I never use scripts for this.  Maybe the script has a bug.

2. You don't need separate system databases for each company

3. Probably insufficient  user security causing it

1. Script has been used on 2 other machines at same time - no issues

2. We don't have separate system databases for each company - we have 1 system database for 3 companies (plus intercompany plus consolidations plus a Play company for each of the live companies) - 10 company databases and 1 system database.

3. We have checked and redone all the user security on her machine - is there something specific we should check?  It is only the one user and two databases.  We have Accpac installed on each machine and then have a shared folder for the customised forms etc.

1. Script has been used on 2 other machines at same time - no issues

2. We don't have separate system databases for each company - we have 1 system database for 3 companies (plus intercompany plus consolidations plus a Play company for each of the live companies) - 10 company databases and 1 system database.

3. We have checked and redone all the user security on her machine - is there something specific we should check?  It is only the one user and two databases.  We have Accpac installed on each machine and then have a shared folder for the customised forms etc.

I repeat, there's no reason to use scripts to set anything up.  Accpac database setup should simply use the SQL server name for _all_ companies, then everything is automatic.

Perhaps I am not being clear.

This was set up by our consultants during initial setup.  We have a SQL database for each company.

The scripts are to create the ODBC connections from each computer to the SQL Server.

You're being clear, and your consultants were wrong.  You don't need to set up separate ODBC DSNs for each database.  In Accpac database setup, you enter the Server name, not a Data Source.  Your consultants made this way too complicated.

I have just checked our SM Administrator Guide and it says "A separate ODBC connection is required for each database ID created in Sage Accpac."

Ok thanks, but I still need to resolve my issue - should I reset up the ODBC connections? Manually?

That's a dumb way of setting it up - to be clear: using the scripts and ODBC DSNs is really a dumb way of doing things. Clearly the consultants are clueless as to what they are doing.

It is far easier to use the SQL server name in Sage 300/Accpac Database Setup - you enter the server name instead of using a Data Source.

The DSN is created automatically when you runs Sage 300/Accpac.

It is easy to change over, edit each database in Database Setup (start with the system database), select Server and then enter the server name - done.

What version????? That must be a very old version because that has not been required since way back when grandad fell off the dinosaur. The Data Source option is there for backward compatibility.

5.6A (Sage Accpac 500 ERP) (Build 52) (Product Update 2)

It's not old, so ignore the manual and do what we said.

Does that also make it easier for running Crystal reports - both from within Accpac and in Crystal Reports itself?  I assume I would have to update all my reports.

Unless you did something very fancy, you don't have to update any reports, the report engine handles the switch between databases.

OK, will look at getting it changed - is there anything I need to be aware of when doing it?  what can I expect?  We are looking at upgrading to 2012 soon so would probably do then.

I still have the issue that one user cannot access 2 databases - I have reset up the ODBC on her machine, checked all the access and we (5 of us) are all part of a group for security so can't see how one user 's permissions are an issue. Am at a loss.  It is not easy for me to log into her computer and try accessing the databases as we don't have roaming profiles and I will need to get set up as an administrator on the laptop.

Is there anything else I can look at - am concerned that changing to using the SQL Server name in database Setup will not fix the issue as it doesn't appear to be an ODBC problem - or am I wrong?

Cheers

Anna

Just do what we told you and your issues will go away.

except don't do anything that Samantha said

We have a KB article that addresses the error "Invalid Database Specifcation"

support.na.sage.com/.../viewdocument.do

To summarize what is in this article, it could be that the Microsoft SQL Native client drive may be damaged. Try installing or repairing it.

Hopefully, this will resolve your issue.

So it turns out that the issue was in Database Setup, the Datasource/Server was set to Server for the two databases with the datasource name as the server name, as well as being a 64-bit machine with the ODBC half set up in the wrong place.  Not sure how or why this happened, but now I know where and what to check  (and we are also looking at changing to using Server as well instead of individual data sources once I have tested in play to make sure all our reports will work).

In reply to Vanessa - I would have thought if the SQL native Client was damaged, then none of the connections would work?

I have _never_ seen a "damaged" native client.  If it's installed, it works.  The only issue with native client is that old versions of Accpac shouldn't use it.

Your reports will work because the Crystal runtime engine resets the database connection automatically.

I ended up fixing up the information in database setup, as it was all over the place and inconsistant - somehting obviously went wrong on the install - still using a Data Source but will be looking to change to the Server name soon - once I have time to fix any customised reports that might have issues.  Thanks for pointing me to database setup.

You ask advice yet you don't accept it????

Umm pretty sure I said in my post that I will be changing to using the Server name shortly.  You don't know about our organisation to know what is potentially involved - it may be simple to you but I have a number of things to consider and sort out first.  Especially considering there is no information on changing from one to the other, how I actually go about doing it and implications if I do some, not others (as we have computers in different locations), so if you could point me to guide that covers all that maybe I can do it sooner.

Go to Database Setup, edit the database and enter the server name. It could not be any simpler.

Do I do that on every desktop? Does everyone need to be logged out/restart etc?  Can I change it back if something goes wrong?  Do I have to do every database at the same time?

No.

Yes logout, no restart.

It won't go wrong!!!!

No.

OK thanks.