July 17, 2003 at 4:13 pm
I had to restore an access db into sql server. ODBC works successfully on client computer but when I start access application i get an error.
Connection failed:
SQLState: ''
SQL Server Error:0
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection; Access to selected database has been
denied.
Any help would be greatly appreciated.
thanks,
Sal
July 18, 2003 at 1:44 am
Hi Sal,
4060 is a login failure. Not sure how sql copes with rights and permissions when restoring from access. Accoring to BOL
quote:
Microsoft Access 2000 requires the installation of either Microsoft Office 2000 Service Release 1 (SR1) or Access 2000 SR1 to operate correctly with SQL Server 2000. If running an earlier version of Access 2000, you cannot test automatic data processing (ADP) applications against SQL Server 2000. You cannot access database diagrams, stored procedures, table designs, or view designs.Other issues to be addressed in a future Access Service Release:
When you run Access 2000 with SR1, you can test ADP applications. You can also alter database diagrams, stored procedures, table designs, or view designs, but you cannot save any changes. A future Access Service Release will allow limited ability to save changes.
The Access 2000 Create Database Wizard cannot successfully create a SQL Server 2000 database. You can work around this by first creating the database using SQL Server Enterprise Manager, and then creating an ADP for the database using the Project (Existing Database) option on the New dialog box in Access 2000.
The Access 2000 Upsizing Wizard does not support upsizing to SQL Server 2000. You can work around this by using Data Transformation Services in the Enterprise Manager to import your MDB database file into SQL Server. You can then rename your MDB tables and create linked tables to the resulting SQL Server database with the same names as your original MDB table names
Hopefully one of the resident Yodas will be able to shed more light.
Laters
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards
July 18, 2003 at 8:09 am
Hi,
Actually, we are using sql server 7.0 and we restored an access 97 databse on to it. We can connect to the database by the application if we keep hitting the cancel button when the error comes up. I just don't understand what is going on. It will let you in if you even try to link the table from a blank database using odbc.
Sal
July 18, 2003 at 8:13 am
quote:
Actually, we are using sql server 7.0 and we restored an access 97 databse on to it.
You can't restore Access 97 database to SQL Server. Can you clarify it?
July 18, 2003 at 9:52 am
Sorry, we restored a sql server database from a backup and the front end is access 97.
Sal
July 18, 2003 at 10:02 am
OK. Go to Access --> open any one of tables in design view --> click properities from toolbar to find out the ODBC name used for linked table to SQL Server.
Go to control panel --> ODBC datasource --> click ODBC name you find from above --> configure --> choose correct authentication mode/username and password --> make sure the test connection is successful.
July 18, 2003 at 11:52 am
Hi,
I did this already..the connection is successfull, which is why this is so frustrating.
July 18, 2003 at 12:27 pm
Go back to Access --> Tools --> Add-Ins --> Linked Table Manager --> Select All --> Click to refresh the link between linked table and SQL Server.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply