Converting Access 2000 to SQL server...

I am converting an Access 2000 app to use SQL server instead of access
tables.
There is a lot of work ahead to get it where I really want it - especially
after browsing this newsgroup and doing some research.
I will be using SQL pass-through queries to replace the existence of link
tables - specifically for the tables that currently contain thousands of
records.
Currrently, I have forms/reports that are based on Access tables. Will I
then have to modify these objects so that they are based on the new pass
through queries I will be developing?
Also, since I am communicating directly with SQL server, am I to assume that
Access SQL will not alwyas work anymore and I must use the Transact SQL that
SQL server uses?
One more question...
When creating recordsets etc... in Access, what is the best method to use
when connecting via ODBC to SQL server? The use of ADO recordsets or is
there something better than that for SQL server?
Thanks,
Brad"Brad Pears" <donotreply@.notreal.com> wrote in message
news:%23Ro9ZTTvEHA.612@.TK2MSFTNGP15.phx.gbl...
> I am converting an Access 2000 app to use SQL server instead of access
> tables.
> There is a lot of work ahead to get it where I really want it - especially
> after browsing this newsgroup and doing some research.
> I will be using SQL pass-through queries to replace the existence of link
> tables - specifically for the tables that currently contain thousands of
> records.
> Currrently, I have forms/reports that are based on Access tables. Will I
> then have to modify these objects so that they are based on the new pass
> through queries I will be developing?
In my Access app, I have used Access Query objects, as SQL Pass-Throughs, as
the RecordSource for dropdowns and reports. For bound forms, it might not
work as well. I don't think the data is updateable.

> Also, since I am communicating directly with SQL server, am I to assume
that
> Access SQL will not alwyas work anymore and I must use the Transact SQL
that
> SQL server uses?
Absolutely! Pass-Through means the SQL will be sent directly to the server,
so it must use syntax the server understands. When using ODBC, the ODBC
driver translates Access SQL for you. See my reply to a previous post of
yours for more details.

> One more question...
> When creating recordsets etc... in Access, what is the best method to use
> when connecting via ODBC to SQL server? The use of ADO recordsets or is
> there something better than that for SQL server?
IMHO ADO is best.

> Thanks,
> Brad
>|||Brad,
We did this and only because the database was getting too big for access.
And it turned out to be even a bigger headache than we thought.
Another problem was that for our type of data (time series, i.e. pretty much
all flattables) msSQL turned out to be horribly slow. If it weren;t for the
size limitation I'd 100% regret leaving access-jet.
Bottom line: Be absolutely sure you really need to convert before you do it.
TSH|||Brad,
Can you please fill me in how did your task go?
I have a similar situation where I need to convert macros, forms,
reports and queries? hat is the most suitable path?
Thanks for the feedback ...
Paul
karipaul wrote:
> * *
karipaul
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1188601.html|||Access objects -- macros, forms, reports, VBA code, etc. -- can't be
converted to SQL Server because there are no equivalents. Queries will
likely need to be rewritten as views or stored procedures.
--Mary
On Thu, 30 Dec 2004 15:46:25 -0600, karipaul
<karipaul.1i3qyp@.mail.mcse.ms> wrote:
[vbcol=seagreen]
>Brad,
>Can you please fill me in how did your task go?
>I have a similar situation where I need to convert macros, forms,
>reports and queries? hat is the most suitable path?
>Thanks for the feedback ...
>Paul
>karipaul wrote:|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.odbc:44108
Hi,
Use a project (.adp). There is a exemble in the help section.
"Brad Pears" <donotreply@.notreal.com> a crit dans le message de news:
%23Ro9ZTTvEHA.612@.TK2MSFTNGP15.phx.gbl...
>I am converting an Access 2000 app to use SQL server instead of access
> tables.
> There is a lot of work ahead to get it where I really want it - especially
> after browsing this newsgroup and doing some research.
> I will be using SQL pass-through queries to replace the existence of link
> tables - specifically for the tables that currently contain thousands of
> records.
> Currrently, I have forms/reports that are based on Access tables. Will I
> then have to modify these objects so that they are based on the new pass
> through queries I will be developing?
> Also, since I am communicating directly with SQL server, am I to assume
> that
> Access SQL will not alwyas work anymore and I must use the Transact SQL
> that
> SQL server uses?
> One more question...
> When creating recordsets etc... in Access, what is the best method to use
> when connecting via ODBC to SQL server? The use of ADO recordsets or is
> there something better than that for SQL server?
> Thanks,
> Brad
>