Access 2003 Project (ADP) vs Access 2007 Project

Thank you for you feedback Bob.

Sounds like ADP and SSMS is working very well for you.

Bob is there a Developers version of SSMS and SQL Server 2005 for a desktop computer. Currently I only have the free Desktop SQL Server 2000 installed on my computer.
 
Last edited:
Thank you for you feedback Bob.

Sounds like ADP and SSMS is working very well for you.

Bob is there a Developers version of SSMS and SQL Server 2005 for a desktop computer. Currently I only have the free Desktop SQL Server 2000 installed on my computer.


Peter,
you can download the Express version (this is free) of both SSMS AND SQL2005. I think the limitation of the express 2005 is that
databases can go only to 4gb and it will only use 1 processor. For desktop development this should work fine.
I googled for it and found the SSMSE at http://www.microsoft.com/downloadS/...AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en.

hth,
..bob
 
You mention DAO... does this mean you had local tables as well as links to an MDB backend on top of ODBC links to SS 2005? The reason I ask this is because I was under the impression that SQL Server only works with ADO. (Please note I am new to SQL server development hence my numerous need for clarification)

I'm not positively sure about ADP but I believe you can't use DAO with ADP. You can't even use Jet in ADP at all. However you can use DAO and/or ADO with .mdb for *any* backend, including SQL Server and as matter of fact, I use both in same application. But it's more complicated than that because Jet engine itself is capable of sending the ODBC requests directly to the driver so in case of bound form, the interface may be actually Jet -> ODBC Driver, rather than VBA -> DAO -> ODBC Driver as would be the case if I used VBA code.

DAO used to support ODBCDirect as well but that's been deprecated. That said, there's nothing stopping you from running Access saved queries against a linked table and still getting quite good performance, providing you understand what is expected from you as the developer in writing the queries and developing a successful client. You may want to read this as well as that for more specific details how to optimize your queries.

I did/do have local tables as well, mostly to provide a local lookup tables so the client need not pester the server to fill every listboxes & comboboxes and other cases where updatability isn't significant. It's easy enough to pass occasional updates to the lookup tables once every while, thus narrowing the amount of data we need to request from the server to only what actually are "live" data, rather than live data *and* static data. In my POV, this is a good and cheap way to improve concurrency & performance because we can distribute more load from server into clients and keep server available for data manipulation where it really counts.

I believe you can get a SQL Server Developer Edition for $50 which includes SSMS.
 
Thank you Bob and Thank you Banana.

Banana I am looking forward to reading your article tomorrow.

Today was a good day.

Peter.
 
Thanks for the ODBC article Banana. The part about optimizing your saved Access Queries so the client doesn't do any evaluation was very important.

Peter.
 
Hello all.

I've read through this thread with interest. Our organization is in the midst of testing Office 2007 before we make the move. I have an Access 2003 ADP connected to a SQL Server 2005 database. I do all of my database design work in SSMS and all of my UI work in Access 2003. I chose this approach becuase I didn't need/want to go to the effort of learing .net

So far the experience has been a bit of a mixed bag (there gotcha's here and there that you don't read about until your'e down and dirty in the minutia of working with ADP's) but I'm generally satisfied.

However...

I'm testing my Access 2003 ADP using a VM of Windows XP with Office 2007 installed. I can open my ADP and use forms to display data but if I try to insert, update or delete data I get an error dialog box which says "<app name> could not log onto the server. Verify that the log on information is correct." Since I'm already able to view data - which means that I'm already logged onto the server, I'm presuming that the error message is bogus.

I am both dbo and sysadmin (the database is runing local on my desktop server right no) so security privilages should not be a problem. The very same ADP run from my natively from my desktop machine using Access 2003 works fine.

Any thoughts about what could be going on?
 
Hello Disa,

>>I'm testing my Access 2003 ADP using a VM of Windows XP with Office 2007 installed. I can open my ADP and use forms to display data but if I try to insert, update or delete data I get an error dialog box which says "<app name> could not log onto the server. Verify that the log on information is correct." Since I'm already able to view data - which means that I'm already logged onto the server, I'm presuming that the error message is bogus.

<<
The only time I've had issues with access to data is when I create a role or schema that a user in the AD does not have access to. As you've stated, you can see the data in the form so..., you obviously have execute privileges on the stored procedure and Select privileges on the table. Is it possible that you do not have insert, update, delete privileges on the table?
.. I don't use VM but I would not think it is part of the problem. Is your SQL DB loaded on the VM or is your .adp connecting to the host machine?

..bob
 
Hmm.

It looks like it. On the VM, I'm looking at the drivers tab of the ODBC utility and I can see an SQL Native Client driver. Version 2008.90.3042.00 SQLNCLI.DLL as well as a SQL server driver. Version 2008.85.1132.00 SQLSRV32.DLL.
 
Hmm.

It looks like it. On the VM, I'm looking at the drivers tab of the ODBC utility and I can see an SQL Native Client driver. Version 2008.90.3042.00 SQLNCLI.DLL as well as a SQL server driver. Version 2008.85.1132.00 SQLSRV32.DLL.

As long as the connection is made through the SQLNCLI driver, then you should be able to update records okay.
 
Lol! Pardon me. I was looking at the ODBC utility of my local machine, not my VM! Looks like the ODBC utility on the VM is locked down by network policies. I'll have to pursue that with our support people. Thanks for the lead!
 
ions,

I have to say that you are going to create confusion when you create several threads all over asking same question in different way or very closely related questions. I don't know if Bob has seen this thread that led you to this conclusion, but you basically started what? 5 new threads in a day? Not really good netiquette, I'm sorry to say.

Also, I would be careful with making broad-brush statements such as this:



especially considering there is no formal "panel" of experts and I know of at least one other MVP who uses ADP. Yes, it's true to say ADP hasn't received any updates since 2002-ish, but I don't think it's as accurate to claim it's dead because it's still supported even in 2007. I will call it dead when I find one day that I can't create .adp in the next version but there's nothing stopping me from doing so in 2007.

FWIW, this MVP agrees that "ADPs are dead."

John Viescas
 

Users who are viewing this thread

Back
Top Bottom