Is it possible to generate a sql script that contains CREATE and INSERT statements for some tables within an Access database?
I need to create a linked server with SQL Server Management Studio 2005 to an Access 95 database, which happens to be password protected at the database level. User level security has not been implemented.
I cannot convert the Access database to a newer version. It is being used by a 3rd party application; so modifying it, in any way, is not allowed.
I've tried using the Jet 4.0 OLE DB Provider and the ODBC OLE DB Provider. The 3rd party application creates a System DSN (with the proper database password), but I've not had any luck in using either method.
If I were using a standard connection string, I think it would look something like this:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Test.mdb';Jet OLEDB:Database Password=####;
I'm fairly certain I need to somehow incorporate Jet OLEDB:Database Password into the linked server setup, but haven't figured out how.
I've posted the scripts I'm using along with the associated error messages below. Any help is greatly appreciated. I'll provide more details if needed, just ask.
Thanks!
Method #1 - Using the Jet 4.0 Provider When I try to run these statements to create the linked server:
sp_dropserver 'Test', 'droplogins';
EXEC sp_addlinkedserver @server = N'Test', @provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'Access DB', @datasrc = N'C:\Test.mdb'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'Test', @useself=N'False',@locallogin=NULL,
@rmtuser=N'Admin', @rmtpassword='####'
GO
I get this error when testing the connection:
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399)
------------------------------
Method #2 - Using the ODBC Provider...
sp_dropserver 'Test', 'droplogins';
EXEC sp_addlinkedserver @server = N'Test', @provider = N'MSDASQL',
@srvproduct = N'ODBC', @datasrc = N'Test:DSN'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'Test', @useself=N'False',@locallogin=NULL,
@rmtuser=N'Admin', @rmtpassword='####'
GO
I get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Test".
OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.". (Microsoft SQL Server, Error: 7303)
My company makes fairly extensive use of an Access + MySQL application that would probably see some significant traffic on the Daily WTF if I posted the source code. The management of users and their permissions is getting out of hand, and I seem to spend more and more time dealing with tweaking these or trying to figure out why someone can't see what they're supposed to see.
It was originally set up to be used by three users in one warehouse. It's now used by over twenty users in four states, with more to be added soon, and the features have been added in a roughly 10-to-1 ratio with the users... The actual core application isn't bad, but managing users is a pain. Access makes a nice front end to the data itself, which is stored on a MySQL backend in our head office. Users have Cisco VPN boxes at satellite branches, and that's been solid as well. Scope has crept from a simple warehouse shipping record to a full-fledged CRM/ERP ...well, I don't suppose you could call this a solution. An emulsion, maybe. If I had the budget, I'd call up SAP and tell them to have at it. That, I'm afraid, is out of the realm of possibility for the foreseeable future.
Following instructions from Google (not always the safest thing to do) I used the 'User-Level Security Wizard' in Access to assign usernames and passwords to various users, which was fine when I started with 4-5 users total and 3 active users. But it's now quite unwieldy. My deepest wish and desire is that there would be some way to authenticate users and assign privilege roles based on Active Directory username and password. I'm told that's impossible. A few Google searches have turned up nothing of note.
I surmise that it should be possible to get some sort of authentication framework using Active Directory because VBA has links to all manner of APIs in Windows. However...is it worth the time and trouble? Has anyone ever gotten this to work, or am I liable to blow up not only my WTF-worthy application but the domain as well?
So - we have an internal company database, the usual kind of stuff: manages clients, phone calls, sales deals and client agreements/schemes.
It's an Access 2000 front-end, and an SQL Server 2000 Standard back-end. Single server, dual Xeon 3.2GHz, 2GB RAM, Windows Server 2003, gets about 40% CPU load all day, spread across the 4 cores visible to the OS (HT).
The back-end database is poorly designed, and has organically grown over 10+ years, maintained by less-than-skilled individuals. It is badly normalised, and some of the obvious problems include tables with tens of thousands of rows with no primary key or index, which are also used heavily in multi-table joins for some of the most heavily used parts of the system (e.g. a call manager application that sits on everyone's second monitor for 8 hours a day and runs a big inefficient query every few seconds).
The front-end is not much better, it's the typical mess of hundreds of forms, nested saved queries, poorly written embedded SQL in the VBA code, dozens of "quirks" etc, and whenever a change is made something unrelated seems to break. We have settled on one MDB that works "well enough" and now have a no-change policy on that as we have no Access heavyweights in-house (and no plans to hire one either).
The company is now slowly growing, increasing numbers of clients, calls etc, as well as a modest increase in the number of concurrent users, and performance has been getting noticeably worse just recently (waiting to move between forms, waiting for lists to populate etc)
Perfmon says:
- Disk transfers per second: between 0 and 30, average 4.
- Current disk queue length: hovers around 1
SQL Server's profiler sees hundreds of thousands of queries every minute. CPU usage on the clients is pretty much zero, indicating it's waiting on server-side queries to execute. I have put this workload through the DB Engine Tuning Advisor, applied its suggestions to a test backup, but this hasn't really made much difference.
By the way, we have a mix of 100MB and gigabit ethernet, all on one subnet, 40 ish users across two floors.
To the question.
As I see it we have two choices to resolve/improve this situation.
- We can scrap it and replace it with an entirely new CRM system, either bespoke or part bespoke
- We can extend the life of this system by chucking hardware at it.
We can build an Intel i7 system with crazy performance numbers for an order of magnitude less cost than replacing the software.
When a new system is eventually developed, it can be hosted on this box, so there's no wasted hardware. A new CRM system keeps getting put off, and off, and off - I don't see that happening for at least a year.
Any thoughts on this situation, especially if you've been here yourself, would be most appreciated.
Thanks
To my surprise and delight I read that an adminsitrator can import (nearly directly) an Access 2007 database into a sharepoint site. Automagically, the database in transformed into lists and views with some table lookup thrown in for good measure. With Access 2007 installed on the client machine, even the forms and what not can still be reused.
To me... this sounds to good to be true.
Has anyone actually dones this? With all this good news, where is the bad stuff and pitfalls to this. Depending on the size of the database, wouldn't this some how "gum up the works" in the SharPoint database?
Sources: http://madhurahuja.blogspot.com/2007/01/adding-data-to-sharepoint-l-ists-in.html http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/17745835-a861-4984-9f44-7291fdae7d07