Microsoft Access as a Front End for SQL Server 2000
-
Fellow SQL dudes, I need to set up MS Access as a front end to SS2K. It also needs to be web enabled.
Is there an easy way to do this? I've looked at the MicroSoft MSDN site and didn't find what I needed. Any ideas?
Thanks, Ron Kinney
-
yes,
in access just select the tables section and then choose import/link tables - you can then create a link to all of your SQL server tables
anything you do in access now (such as building a form etc) will treat the linked tables as local tables.
MVDBA
-
jsut remember to make sure you select the correct primary key when you link the tables (or views).
and if you do change the tables then you need to refresh your linked tables .
MVDBA
-
Access is a great "inside-the-LAN" front end for SQL Server. I used it for this for years.
There's no decent way to web-enable the whole thing, howerver.
You will need to create a separate web app for it. I recommend PHP if you have no other first choice for a web application software 'cause it free, powerful, cross-platform and faster than any Microsoft product. See http://www.php.net.
G. Milner
-
A bug with Access 2000 (and maybe 2002, but fixed in 2003) with linked SQL Server tables might cause a table that you've linked to, when you change data, to cause all the fields and records in a page or the whole table to appear to be "#Deleted".
A workaround is to add a timestamp field to the table. Access uses the timestamp field to help determine whether the data has been modified by another user...
It's in the KB...
-
If you use Access 2000 (not sure about later versions), then you can create an adp (access database project) instead of an mdb. With an adp you can setup a connection to your SQL Server and the tables and sp etc will be viewable from within Access... Then use ADO within the code to interogate the database.
This provides a further benefit that you can use a lot of the VB code within Access and the SPs for you web pages (assuming you implement this in ASP)
-
The preferred solution, as noted by chappu, is to use an Access .adp (Project), NOT a .mdb with ODBC-linked tables. Projects (.adp) use OLE DB for faster performance than ODBC.
Second, you can use ADO and SQL-DMO in Access VBA for really *complete* control over the server.
Third, you certainly can access your SQL Server database over the Internet (using a variety of protocols [including TCP] - look into SSL and other forms of encrypted network protocols here) from anywhere in the world, but you need to learn about the security implications of this, and you may need to adjust the firewall in front of your server. There are many articles on the web about this approach.
Many people have been scared away from Internet-enabling their SQL Server apps, especially since the Slammer situation. However, this problem can be mitigated once you learn about the patches, ports, proxy servers etc. You can also look into VPN access to your server, but this has its own plusses and minuses.
The bottom line is that you can certainly use an Access project to connect to SQL Server the Internet, but, as in any Internet solution, you need to make sure that you have your system locked down against unauthorized access.
If you decide to go for an n-tier Web solution, you will massively increase the amount of development time for your project, and you will lose a massive amount of native Access responsiveness and functionality.
However, a web solution is good if your Access app can't be installed on client computers, or if you expect to have large numbers of users, and you can't handle the installation headaches. Installing a robust Access client front end is not a trivial task, and you *will* need to spend time on creating a robust client installation program with third-party tools.
In contrast, as we all know, Web apps may be engineered to require little or no installation, but the apps are generally significantly more expensive and less powerful.
And finally, I should note that some developers have created web-based installations for their Access apps, so the installation (and updates) takes place on the click of a web link.
Good Luck
-
Check this out.
Microsoft Access Developer's Guide to SQL Server by Mary Chipman and Andy Baron.
Phillip L. Hagan -
In the bad old days of Access 97 and earlier there used to be horrendous problems with MS Access causing locking issues in the SQL Server database.
Has this been fixed yet?
As far as web enabling SQL Server, MS Press Programming SQL Server with XML goes all the way through telling you all about updatagrams etc and then right at the end of the book tells you not to enable it in a live environment because of the risk of security breaches.
Personally, I've always taken the data security thing very seriously, and although I can lock down a SQL Server database I know that there are some extremely bright people out there who can probably circumvent anything I can do.
Try doing a search for cracking sql server passwords and you will see what I mean.
-
here is the solution of the Hang Problem between Access and SQL server 2000 or 7
When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!
if you use a query in Access and make a report based on it, and in that query "properties" you choose "No locks" in "Record locks", the query will lock some tables or pages (so although you choose "No locks", it still makes locks!! -the lock type is IS lock- .. because it is a bug in Access with SQL Server)
the best thing is to run the query on the server (by making Pass-Through Query in Access) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others)
.you will write something like this in your Pass-Through Query in Access:
select Invoices.*, Situation.*
from Situation with (nolock) INNER JOIN Invoices with (nolock)
ON Situation.SituationID = Invoices.Situation
where Situation.SituationID =1
Disadv:
- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.
Adv:
- no hang at all
- very fast Response
- Little summary for Locks in SQL Log file.
also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.
ALSO:
try to put the DATA and the LOG file of SQL Database on a partition that is Formatted with 32 K or 64 K Cluster Size (not the defalut = 4 K)
because the default Page size in SQL in memory is 8K ,
And If u use the defulat Cluster Size while formating partioin, then to get 1 page (8K), you need 2 hits for the HardDisk (4 K each)
But If u use 32 K Cluster Size while formatiing partioin, then on 1 hit for the HardDisk (32 K each) , you get 4 pages into Memory (more faster ... because the slowest thing that slow the whole system is to read/write from Hard Disk)
I use 32 K not 64 K.. because with 64 K you may have a lot of empty pages in memory ( lost in memeory and your DB may be graw more)
this of course will help you to solve the problem of hang more and more .. bye
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Use Access Database Project. It is so cool. You could use stored Procedure.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply