Access to SQL server

[Nate]

Can an application which is build is MS Access run against a SQL server database
without changing the SQL code?

Also would you use Access in a five user environment. I don't know the size
of the database, it is probably under 100k, but I know that about 2000 records
are added per month and there are probably 30,000 records now. ?

Thanks

Nate

[Ron]

"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

Nate,
1. It would depend upon the exact nature of the individual SQL statement

in Access some will, some will not. Neither T-SQL or Access SQL are standard
compliant. Functions in Access such as 'Trim' do not exist in T-SQL where
you must use 'RTrim' and / or 'LTrim'. To test the length of a returned
data value (within the SQL statement) T-SQL uses 'DataLength' while you
can use 'Len' in Access.
2. Access in a 5 user environment should pose no problem if you are
careful as to what locking (optimistic or pessimistic) you are employing,
and when invoked.
3. Max size of an Access 97 database is one (1) gig to you can do
some calculations and see wether your back is against the wall or not.

Hope this helps

[David Voo]

"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

Hi Nate,

It all depends on how you coded the applcation in Access earlier since we
already know that software application programming can be a tedious thing
if you have not do it with OOP concept, the only possible way to reduce the
efforts to change to SQL Server based down to minimum if you create your
Access application in OOP way.

Anyhow, there are some changes n makeup you have to workout especially some
database connection, if you used ADODB in Access then you will save tons
of works when come to SQL Server.

Good luck!

[Rick Dobson]

Hi Nate,

You can definitely run Access against a SQL Server database without changing
the SQL code. The trick is to use Access projects and the right version
of Access for the SQL Server version you are using.

Access projects let an Access application work with SQL Server tables, views,
stored procedures, and user-defined functions. In addition, with SQL-DMO,
you can programmatically administer SQL Server. You can achieve much of
the same administrative functionality by running T-SQL statements from ADO
Connection objects. Another option is to run SQL Server stored procedures
with parameters and ADO Command objects for faster performance and more flexibility.

When working with SQL Server 7, Access 2000 is sufficient. While you can
get patches to make Access 2000 work with SQL Server 2000, Access XP delivers
more native SQL Server 2000. For example, Access XP readily permits working
with user-defined functions from its UI, but Access 2000 does not know about
user-defined functions in its UI.

As far as the number of users and the size of the database you have several
options. The Microsoft Data Engine (MSDE) is free and ships with
Office 2000. It handles 5 users without any throttling, but Microsoft says
performance degrades after 5 users. I have anecdotal reports of applications
achieving acceptable performance with many more users. Office XP ships with
an updated version of MSDE that reflects SQL Server 2000 features, but is
still free to Office owners and requires no client licenses for use. You
can, of course, run Access projects with any version of SQL Server. Performance
depends on a wide range of factors, including database design, programming
conventions, and hardware. 30,000 records is not likely to put a strain
on any version of SQL Server -- even MSDE.

My site, www.programmingmsaccess.com, offers numerous code samples illustrating
techniques for programming SQL Server with Microsoft Access. My most recent
book, Professional SQL Server Development with Access 2000, has hundreds
of code samples, design strategies, and development tips for those who want
to build SQL Server solutions with Access. I wouldn't have written the book
if I didn't think the topic was important for both Access developers who
want to move up to SQL Server and SQL Server DBAs who want to bring the power
or RAD development with Access to their SQL Server databases. Happily, I
have a growing number of fans for the book and the solutions strategies that
it demonstrates.

Rick Dobson
www.programmingmsaccess.com


"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

[Tom Kelley]

"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

[tim Curtin]

"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate



Hello Nate:
Some of the queries may need to be changed. The Access driver does a pretty
good job of converting the sql. If the database is 'Mission Critical' and
could mean the death of the company if it is corrupt, then get the data out
of Access and into SQL Server. Access mdb's can be irreparable. Access 2k
can handle 2gb file size and prior versions 1gb. Nothing can be more expensive
than lost data. Access is nice for small user and 'department' level systems.
Think SQL.....

[Patrick]

"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

Hi,

Access 2000 has a "project" mode wich is ADO built.
This means,you have to modify some code from DAO to ADO.

But then you can have more then 100 users un a database as big as you want
(currently I work with 300 MB).

Patrick

[Dev]

"David Voo" <david@download.com.my> wrote:
>
>"Nate" <nczimm@msn.com> wrote:
>>
>>Can an application which is build is MS Access run against a SQL server
>database
>>without changing the SQL code?
>>
>>Also would you use Access in a five user environment. I don't know the
size
>>of the database, it is probably under 100k, but I know that about 2000
>records
>>are added per month and there are probably 30,000 records now. ?
>>
>>Thanks
>>
>>Nate
>
>Hi Nate,
>
>It all depends on how you coded the applcation in Access earlier since we
>already know that software application programming can be a tedious thing
>if you have not do it with OOP concept, the only possible way to reduce
the
>efforts to change to SQL Server based down to minimum if you create your
>Access application in OOP way.
>
>Anyhow, there are some changes n makeup you have to workout especially some
>database connection, if you used ADODB in Access then you will save tons
>of works when come to SQL Server.
>
>Good luck!

Hi
If u want to use Access SQL statments on SQL server try using link tables
Dev

[Arthur Hill, CPA MCSD]

I wrote a system for Ford at the Fairlane Training and Development Center
in Access 2.0 that had fifteen users and 200,000 records with 120 + tables,
240 + queries and 100 + reports running on 486 servers at best and 386 workstations
and it ran the place for five years. Access 2000 on modern workstations
and servers can handle five users in most circumstances without any strain
if you design you databases correctly with all the correct indexes and other
optimization features.

[Jmills]

Yes, you can use MS SQL as your database on an Access front end. You need
to link the SQL tables into Access. I do this by first creating an System
DSN in the ODBC manager for my SQL Databae, then link the table using that
ODBC.

Yes you can execute the Stored Procedures from Acess. Using DAO. Or ADO
if you are using Access 2000. This is true for the other Office products
as well.

If your stored proc uses Temp tables that you wish Access to see, you will
need to link those as well. You can do this by running some code in the
query analizer to create the temp table, and keep it open while you link
your Access table to the temp one. Remember to keep the temp table open
all you have to do is keep the connection that created the temp table open,
as long as your code does't drop the table.

Access works for a five user enviroment. I've found that problems don't
start arising until the user count gets to 20 or so. MS states that it can
handle upwards of 250, but I don't recommend trying that. As always, SQL
is going to handle multiple users better than access. Also, look at how
Access is used across you network. Each machine that uses it will pull the
database across the network to be viewed in the workstation's memory. It
could degrade your network performance. Using SQL will allow you to pull
smaller chunks of data. The best performance is SQL with a VB or VC front
end, if you can. I know its not always possible.

HTH

-Joseph




"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

[Danny J. Lesandrini]

Nate:

That depends.

Are you linked to SQL Server through ODBC? If so, then ODBC serves
as a "translator" to modify the Access SQL to be valid for SQL Server.
For example, dates in Access are limited by the pound sign (#) whereas
in SQL Server it's the single Quote ('). ODBC makes this substitution
for you.

On the other hand, if you are using ADO with the native SQL Server
provider, then you will need to write your SQL in native SQL Server
syntax. Another example of that which catches people is the wildcard
character. In MS Access, you use an asteriks (*), but in SQL Server
you need to use the percent sign (%). So, when you issue this SQL
using ADO from MS Access, it will fail:

SELECT * FROM Authors WHERE au_FName Like 'D*"

It should read

SELECT * FROM Authors WHERE au_FName Like 'D%"

Stick with ODBC and you don't need to learn these differences, but
your code will be slower because you are invoking the translator. I
created a SQL parser to reformat my MS Access SQL into a "pretty"
form and to convert Access syntax to SQL Server. It's not perfect,
but it's free.

Go to my download site and download my DataFast Utility:
http://24.22.189.23/datafast/datafastutility.asp

Follow the instructions to install it as an Addin. The zip contains
Access 97 and 2000 versions.

HTH

Danny J. Lesandrini
Downloads: http://datafastdownloads.cjb.net
Articles: http://www.swynk.com/friends/Lesandrini/


"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

[Davis Salks]

In many cases, no problem. However, depending on your querie's features, execution
could get split-up between the client and the server. Worst case is where
none of the query runs at the server. It will run, but you will have significant
performance issues.

[Henry K]

Yes, it is possible to modify Access to run against SQL server w/o modifing
any code. You will need to move your table to SQL server and re-link them
back into Access. However, in your case you do not need SQL Server, it will
be a waste of money. Access works fine with 5 users.

"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

[James Remsey]

I have an application that I built entirely in MS Access. It was a contract
management application which stored approximamtely 500 active records (records
changed on a daily basis) and about 2000 plus inactive or closed records.
There were about 20 people hitting it daily across the network. Because
of the number of people using it I had to upsize it to SQL Server. What
I did was Import the database into SQL Server and then in Access I linked
to the tables. Linking will also include the the owner in the table name
when linking. I removed the onwer name from the table names and verified
all the linked tables were named as before and verified all my field names
were the same. I just double checked! I had a few changes into of my SQL
Queries when I combined fields in the query but other than that it woked.
Ther was also a considerable gain in speed, especially since I had over
extended Access.

Basically just link to the SQL Database table from Access!

Jim Remsey
development@theimageco.com



"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate

[a]

Don't do it.

A customer of mine had an access system, moved the DB part to SQL7, everything
was okay. Upgraded to SQL2000 and had constant locking problems.

I was so bad they eventually had to back everything out and have a rewrite
in process.

"Nate" <nczimm@msn.com> wrote:
>
>Can an application which is build is MS Access run against a SQL server
database
>without changing the SQL code?
>
>Also would you use Access in a five user environment. I don't know the size
>of the database, it is probably under 100k, but I know that about 2000
records
>are added per month and there are probably 30,000 records now. ?
>
>Thanks
>
>Nate