Access to SQL server

[Alan]

Nate,

You can use the upsize wizard, use DTS in SQL, use any number of modeling
tools or simply create the table structure in SQL to match your Access db.
In any case, so long as the db structure is the same, you will be able to
link the tables via ODBC, and your MS Access app ought to work just as it
did previously. From that point you could then adapt your application to
some of the beneficial features of MS-SQL Server as time allows.

However, with the usage and data size you have outlined, assuming a reasonably
prudent database programming style, MS Access is absolutely acceptable and
robust enough to serve your stated requirements.

Alan
astruthers@infogen.net

"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]

"Alan" <astruthers@infogen.net> wrote:
>
>Nate,
>
>You can use the upsize wizard, use DTS in SQL, use any number of modeling
>tools or simply create the table structure in SQL to match your Access db.
> In any case, so long as the db structure is the same, you will be able
to
>link the tables via ODBC, and your MS Access app ought to work just as it
>did previously. From that point you could then adapt your application to
>some of the beneficial features of MS-SQL Server as time allows.
>
>However, with the usage and data size you have outlined, assuming a reasonably
>prudent database programming style, MS Access is absolutely acceptable and
>robust enough to serve your stated requirements.
>
>Alan
>astruthers@infogen.net
>
>"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
>

[CyberDude]

Hi Nate,

do you mean that you would like to have an Access and SQL database server,
or would you like to use access as your desktop interface with your SQL server?

[Paul]

"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

[Paul]

Nate,
Use ODBC direct and pass through queries to get the best performance.
Remember that using ODBC direct and Pass throughs you have to learn the native
TSQL. I'm an long time Access user and TSQL is a great addition to your skills.
Avoid linking to SQL server tables if you can help it, and do it all in VBA
if you can.

Paul

"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

[ADH]

"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,
I read the answers to your (important) question.
1 Major argument to add: Network !
As you know when accessing Jet all the data involved moves between server
and client. This on overloaded or "Busy" networks can be a killing factor.
SQL Server, when using smart store procedures sends to you only (and only)
the data you really asked for.
This by itselves is a firm and main reason to make the migration.
I am sure you will in some conditions see improvments in response time even
with a single client.

[Sven Aelterman]

You may have to change the wildcards in your WHERE clause: from * to % and
from ? to _. The other parts will remain unchanged.

Using Access in a 5-user environment is not a problem, but just don't go
and use it over the Internet/intranet. Use a client/server design.

The size of the database is usually not an issue, as long as you compact
it from time to time. I have had a 50 MB Access mdb running in a client/server
environment with 7 users.

Greets

Sven.

"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

[Rich]

Nate -

You can, in fact, use Access to bump up against a SQL Server database - but
the reference to the SQL server tables is a bit tricky.

Can you do it without changing your existing code...sure.

The reason it is tricky is this:

You need to use ODBC to connect to the SQL db and when you LINK to those
tables, Access creates the link with a dbo_ prefix. For instance, a CUSTOMERS
table in SQL Server can be linked from Access, but when it does, it links
as "DBO_CUSTOMERS".

These names, however, are just nicknames, if you will, and can be renamed
to whatever you want. So you could simply rename the link to "CUSTOMERS"
after the link is made.

There are some other tricky parts, including differences between how Access
treats some data formats (i.e,. a NUMERIC field in a table) and how SQL Server
treats them. Unfortunately, I haven't found a good cross reference as to
the differences...but they do exist.

My final comment, however, isn't on the ABILITY to link to SQL Server, but
more so the RATIONALE. Linking to a SQL Server database isn't going to solve
other problems, such as performance, scalability, etc. - because you are
still confined to the limitations of the Access (JET-based) db engine. While
the data will reside in the SQL Server tables, the processing still occurs
in Access.

Furthermore, you can't link to STORED PROCS or several other performance
enhancing features you gain by using SQL Server.

As for your final question...regarding volume...yes, Access is PLENTY capable
of managing 5 users with the limited growth you have mentioned.

As a general rule, you should probably tend away from Access once your tables
grow beyond 100,000 rows of data.

Hope that helps. If you have more questions, please feel free to contact
me, or the consulting firm I work for, directly.

Our web site is: www.manleygroup.com and my email address is: rich.anderson@manleygroup.com

Regards,
Rich



"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

[Paul Mc]

>My final comment, however, isn't on the ABILITY to link to SQL Server, but
>more so the RATIONALE. Linking to a SQL Server database isn't going to
solve
>other problems, such as performance, scalability, etc. - because you are
>still confined to the limitations of the Access (JET-based) db engine.
While
>the data will reside in the SQL Server tables, the processing still occurs
>in Access.
>
>Furthermore, you can't link to STORED PROCS or several other performance
>enhancing features you gain by using SQL Server.

G'day,

This is not all strictly true. For one thing, you can use pass trough queries
to explicitly send SQL for server side processing. This includes the ability
to execute stored procedures (I am not sure why you would try to actually
Link to an SP).

Additionally, if you run the profiler and do some testing you will see that
if you send Jet a query that contains only linked tables from the server,
and no Jet/Access specific stuff(such as crosstab queries, or functions in
the select list/where clause) then the query IS sent to the server for execution
- Jet is smart enough for that, anyway.

Cheers,
Paul Mc

[Mukesh]

Hi

You can use linked tables to access data from the SQL Server, though for
your kind of application/usage Acess is sufficient for now and may be an
year.

Hope this helps

Take Care


"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

[S. Stewart]

"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

Yes, you can use an Access front end with a SQL Server back end. I am working
on a project tracking application now that has about 25-35 concurrent users
at one time, and there has been over 56,000 transactions added in the last
2 months. Some of the SQL will need to be changed, but most will work in
both Access and in SQL Server.

S. Stewart

[Ray Mogarte]

Nate,

We are currently using Access (developed under VB5) for a four user environment
and very soon we will add another user. Our access database is already nearing
one gigabyte. We have one table that has more than 200,000 records. So far
we have not encountered any problem yet. But still, we do not know as to
how much more can an Access database handle.

Regards,
Ray
"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

[Jo Goos]

With good database design and tuning, theoreticaly I think you can create
a 2 GB Access database for 100-users. The MSDN Library (http://msdn.microsoft.com/)
gives you a comparison between SQL Server and Access.

Practicaly, I experienced a problem (occurred very rarely) when a user connected
to an Access database. Access writes some connected user information in
an .ldb file. It was this .ldb file that caused the error because multiple
users where trying to connect to the database at the same time.

For a five-user environment, I would recommend Access.

Kind Regards,
Jo Goos



"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

[Joe Porschart]

All you have to do is replace the tables locally with links to the tables
on the SQL Server. Make sure you name the links the same as the local tables
so you don't have to change the code. You might want to check into pass through
queries for performance.

Access works fine with multiple users. The problem with Access, last I
checked, is that it has a limit on how large it can get. Once it has reached
that max it stops working. Also, you will notice it grows in size every time
you use the application which requires constant needs to compact the database.
I don't recommend using Access for anything more than a tool for development.

Joe

"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

[Robert Dennis]

You can run MS Access against SQL Server without changing your SQL queries.
To run MSSQL queries in the MSSQL syntax use Pass Through queries. Also autonumbers
do not work the same, in MSSQL an "autonumber" field is assigned a value
by an index after the record is actually inserted.
Assuming that you have liked tables where everyone uses the same "back end"
database and has a copy of the "front end" on each machine you can easily
support 5 simultaneous users. I have found a little performance degredation
after about 12-15 users. One of the things that will help performance greatly
is to create forms that dymamically change the source to pull only the record
that you are working with.

Robert

"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