Sql server as back end

I have a mdb file made by access 2003. It has five tables having relations
between them, 4000 parameter queries, and 100 forms, which are used for
parameter queries. I want to go to the client-server mode using sql server at
the back end and access user interface should be in fornt end. For this what
should I do? How the queries will run smoothly in this new situation? Whether
their syntax need to be changed? Anybody can help me anyway? Any helpful
suggestion will be appriciated.
With regards,
--
nandini

First, I have a utility I built as an Add In which includes a function to analyze all
queries for migration to SQL Server. It basically gives you meta-data on the
aspects of them that might cause a problem, and writes a potential script for
creating Stored Procs out of them.

http://www.amazecreations.com/datafa...astUtility.zip

That having been said, here's what you have to look out for:

1) If the query needs to be updateable, you'll need to convert it to a view, not
a stored proc. (SPs are great for Combo and List box row sources)

2) Built-in functions of Access that are sometimes used in queries will break
in a SQL Server script. Examples: InStr() IIf() Nz()

There are replacements for these, but those will have to be recoded by hand.

3) User defined functions are sometimes used in Access queries by advanced
users / developers. Of course, these will have to be rebuilt in SQL Server. If
not included as In-Line modifications to the SQL, then as SQL Functions.

4) It's common in Access to build queries on queries. If you do that, and want to
reconstruct the heirachy on SQL Server views, you'll have to do it in the right
order. Base queries must be converted to views first, then queries that use
the base queries may be converted to views next.

Again, I think the utility referenced above does some analysis to see which queries
are dependent on which other queries. I did the conversion of 1000 queries using
this tool, and I know of no other way to do it. It involves a lot of hard work.
--
Danny J Lesandrini

www.amazecreations.com



"Nandini" wrote ...
I have a mdb file made by access 2003. It has five tables having relations
between them, 4000 parameter queries, and 100 forms, which are used for
parameter queries. I want to go to the client-server mode using sql server at
the back end and access user interface should be in fornt end. For this what
should I do? How the queries will run smoothly in this new situation? Whether
their syntax need to be changed? Anybody can help me anyway? Any helpful
suggestion will be appriciated.
With regards,
--
nandini

Danny,

Correct me if I'm wrong, but most of those queries will still work if
Nandini moves the data to SQL Server, and then links to the tables.

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't they?

--
Dale

email address is invalid
Please reply to newsgroup only.



"Danny Lesandrini" wrote:

First, I have a utility I built as an Add In which includes a function to analyze all
queries for migration to SQL Server. It basically gives you meta-data on the
aspects of them that might cause a problem, and writes a potential script for
creating Stored Procs out of them.

http://www.amazecreations.com/datafa...astUtility.zip

That having been said, here's what you have to look out for:

1) If the query needs to be updateable, you'll need to convert it to a view, not
a stored proc. (SPs are great for Combo and List box row sources)

2) Built-in functions of Access that are sometimes used in queries will break
in a SQL Server script. Examples: InStr() IIf() Nz()

There are replacements for these, but those will have to be recoded by hand.

3) User defined functions are sometimes used in Access queries by advanced
users / developers. Of course, these will have to be rebuilt in SQL Server. If
not included as In-Line modifications to the SQL, then as SQL Functions.

4) It's common in Access to build queries on queries. If you do that, and want to
reconstruct the heirachy on SQL Server views, you'll have to do it in the right
order. Base queries must be converted to views first, then queries that use
the base queries may be converted to views next.

Again, I think the utility referenced above does some analysis to see which queries
are dependent on which other queries. I did the conversion of 1000 queries using
this tool, and I know of no other way to do it. It involves a lot of hard work.
--
Danny J Lesandrini

www.amazecreations.com



"Nandini" wrote ...
I have a mdb file made by access 2003. It has five tables having relations
between them, 4000 parameter queries, and 100 forms, which are used for
parameter queries. I want to go to the client-server mode using sql server at
the back end and access user interface should be in fornt end. For this what
should I do? How the queries will run smoothly in this new situation? Whether
their syntax need to be changed? Anybody can help me anyway? Any helpful
suggestion will be appriciated.
With regards,
--
nandini

Thanks to everybody for giving suggestions.
As I am not a professional developer, your suggestion is most suitable for
me. In this regard I want to know the steps for moving data to the sql server
and then the process of linking the tables. Please help me much more.
With best regards,
--
nandini


"Dale Fye" wrote:

Danny,

Correct me if I'm wrong, but most of those queries will still work if
Nandini moves the data to SQL Server, and then links to the tables.

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't they?

--
Dale

email address is invalid
Please reply to newsgroup only.



"Danny Lesandrini" wrote:

First, I have a utility I built as an Add In which includes a function to analyze all
queries for migration to SQL Server. It basically gives you meta-data on the
aspects of them that might cause a problem, and writes a potential script for
creating Stored Procs out of them.

http://www.amazecreations.com/datafa...astUtility.zip

That having been said, here's what you have to look out for:

1) If the query needs to be updateable, you'll need to convert it to a view, not
a stored proc. (SPs are great for Combo and List box row sources)

2) Built-in functions of Access that are sometimes used in queries will break
in a SQL Server script. Examples: InStr() IIf() Nz()

There are replacements for these, but those will have to be recoded by hand.

3) User defined functions are sometimes used in Access queries by advanced
users / developers. Of course, these will have to be rebuilt in SQL Server. If
not included as In-Line modifications to the SQL, then as SQL Functions.

4) It's common in Access to build queries on queries. If you do that, and want to
reconstruct the heirachy on SQL Server views, you'll have to do it in the right
order. Base queries must be converted to views first, then queries that use
the base queries may be converted to views next.

Again, I think the utility referenced above does some analysis to see which queries
are dependent on which other queries. I did the conversion of 1000 queries using
this tool, and I know of no other way to do it. It involves a lot of hard work.
--
Danny J Lesandrini

www.amazecreations.com



"Nandini" wrote ...
I have a mdb file made by access 2003. It has five tables having relations
between them, 4000 parameter queries, and 100 forms, which are used for
parameter queries. I want to go to the client-server mode using sql server at
the back end and access user interface should be in fornt end. For this what
should I do? How the queries will run smoothly in this new situation? Whether
their syntax need to be changed? Anybody can help me anyway? Any helpful
suggestion will be appriciated.
With regards,
--
nandini

When you hear hoof-beats, think horses, not zebras!

I was thinking "zebras". Dale, you're absolutely correct and that's the lowest
impact approach. The reason I upsized things to SQL Views and Procs was
to leverage the server's power and speed up the app.

So even though her linked-to-SQLServer queries will work fine, there's an
advantage to analyzing things, especially where performance lags.

--
Danny J Lesandrini

www.amazecreations.com



"Dale Fye" wrote ...
Danny,

Correct me if I'm wrong, but most of those queries will still work if
Nandini moves the data to SQL Server, and then links to the tables.

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't they?

--
Dale

email address is invalid
Please reply to newsgroup only.



"Danny Lesandrini" wrote:

First, I have a utility I built as an Add In which includes a function to analyze all
queries for migration to SQL Server. It basically gives you meta-data on the
aspects of them that might cause a problem, and writes a potential script for
creating Stored Procs out of them.

http://www.amazecreations.com/datafa...astUtility.zip

That having been said, here's what you have to look out for:

1) If the query needs to be updateable, you'll need to convert it to a view, not
a stored proc. (SPs are great for Combo and List box row sources)

2) Built-in functions of Access that are sometimes used in queries will break
in a SQL Server script. Examples: InStr() IIf() Nz()

There are replacements for these, but those will have to be recoded by hand.

3) User defined functions are sometimes used in Access queries by advanced
users / developers. Of course, these will have to be rebuilt in SQL Server. If
not included as In-Line modifications to the SQL, then as SQL Functions.

4) It's common in Access to build queries on queries. If you do that, and want to
reconstruct the heirachy on SQL Server views, you'll have to do it in the right
order. Base queries must be converted to views first, then queries that use
the base queries may be converted to views next.

Again, I think the utility referenced above does some analysis to see which queries
are dependent on which other queries. I did the conversion of 1000 queries using
this tool, and I know of no other way to do it. It involves a lot of hard work.
--
Danny J Lesandrini

www.amazecreations.com



"Nandini" wrote ...
I have a mdb file made by access 2003. It has five tables having relations
between them, 4000 parameter queries, and 100 forms, which are used for
parameter queries. I want to go to the client-server mode using sql server at
the back end and access user interface should be in fornt end. For this what
should I do? How the queries will run smoothly in this new situation? Whether
their syntax need to be changed? Anybody can help me anyway? Any helpful
suggestion will be appriciated.
With regards,
--
nandini

Nandini:

There is an option in the Tools menu, Database Utilities, for Upsize to SQL Server.
This may depend on which version of Access you have, but it's been around so long,
I forget how far back you have to go before it disappears.

A wizard will walk you through the process. It's pretty simple. After it's done, you may
have questions about things that didn't upsize, but that's a different matter. Post back
if you run into difficulties.
--
Danny J Lesandrini

www.amazecreations.com



"Nandini" wrote ...
Thanks to everybody for giving suggestions.
As I am not a professional developer, your suggestion is most suitable for
me. In this regard I want to know the steps for moving data to the sql server
and then the process of linking the tables. Please help me much more.
With best regards,
--
nandini


"Dale Fye" wrote:

Danny,

Correct me if I'm wrong, but most of those queries will still work if
Nandini moves the data to SQL Server, and then links to the tables.

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't they?

--
Dale

email address is invalid
Please reply to newsgroup only.



"Danny Lesandrini" wrote:

First, I have a utility I built as an Add In which includes a function to analyze all
queries for migration to SQL Server. It basically gives you meta-data on the
aspects of them that might cause a problem, and writes a potential script for
creating Stored Procs out of them.

http://www.amazecreations.com/datafa...astUtility.zip

That having been said, here's what you have to look out for:

1) If the query needs to be updateable, you'll need to convert it to a view, not
a stored proc. (SPs are great for Combo and List box row sources)

2) Built-in functions of Access that are sometimes used in queries will break
in a SQL Server script. Examples: InStr() IIf() Nz()

There are replacements for these, but those will have to be recoded by hand.

3) User defined functions are sometimes used in Access queries by advanced
users / developers. Of course, these will have to be rebuilt in SQL Server. If
not included as In-Line modifications to the SQL, then as SQL Functions.

4) It's common in Access to build queries on queries. If you do that, and want to
reconstruct the heirachy on SQL Server views, you'll have to do it in the right
order. Base queries must be converted to views first, then queries that use
the base queries may be converted to views next.

Again, I think the utility referenced above does some analysis to see which queries
are dependent on which other queries. I did the conversion of 1000 queries using
this tool, and I know of no other way to do it. It involves a lot of hard work.
--
Danny J Lesandrini

www.amazecreations.com



"Nandini" wrote ...
I have a mdb file made by access 2003. It has five tables having relations
between them, 4000 parameter queries, and 100 forms, which are used for
parameter queries. I want to go to the client-server mode using sql server at
the back end and access user interface should be in fornt end. For this what
should I do? How the queries will run smoothly in this new situation? Whether
their syntax need to be changed? Anybody can help me anyway? Any helpful
suggestion will be appriciated.
With regards,
--
nandini

Dale,

I believe they will work as Views only, this may or may not be a problem.
As for the dbo_ prefix SOMEWHERE I have a module to remove that. I think I
will look for that and post it on my web site. I don't remember where I got
it but when I find it will tell me.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Dale Fye" wrote in message
...
Danny,

Correct me if I'm wrong, but most of those queries will still work if
Nandini moves the data to SQL Server, and then links to the tables.

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go
in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't
they?

--
Dale

email address is invalid
Please reply to newsgroup only.



"Danny Lesandrini" wrote:

First, I have a utility I built as an Add In which includes a function to
analyze all
queries for migration to SQL Server. It basically gives you meta-data on
the
aspects of them that might cause a problem, and writes a potential script
for
creating Stored Procs out of them.

http://www.amazecreations.com/datafa...astUtility.zip

That having been said, here's what you have to look out for:

1) If the query needs to be updateable, you'll need to convert it to a
view, not
a stored proc. (SPs are great for Combo and List box row sources)

2) Built-in functions of Access that are sometimes used in queries will
break
in a SQL Server script. Examples: InStr() IIf() Nz()

There are replacements for these, but those will have to be recoded
by hand.

3) User defined functions are sometimes used in Access queries by
advanced
users / developers. Of course, these will have to be rebuilt in
SQL Server. If
not included as In-Line modifications to the SQL, then as SQL
Functions.

4) It's common in Access to build queries on queries. If you do that,
and want to
reconstruct the heirachy on SQL Server views, you'll have to do it
in the right
order. Base queries must be converted to views first, then queries
that use
the base queries may be converted to views next.

Again, I think the utility referenced above does some analysis to see
which queries
are dependent on which other queries. I did the conversion of 1000
queries using
this tool, and I know of no other way to do it. It involves a lot of
hard work.
--
Danny J Lesandrini

www.amazecreations.com



"Nandini" wrote ...
I have a mdb file made by access 2003. It has five tables having
relations
between them, 4000 parameter queries, and 100 forms, which are used for
parameter queries. I want to go to the client-server mode using sql
server at
the back end and access user interface should be in fornt end. For this
what
should I do? How the queries will run smoothly in this new situation?
Whether
their syntax need to be changed? Anybody can help me anyway? Any
helpful
suggestion will be appriciated.
With regards,
--
nandini

On Tue, 17 Feb 2009 10:21:33 -0800, Dale Fye wrote:

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't they?

As a public service...

Public Sub RenameSQLTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "dbo_" Then
tdf.Name = Mid(tdf.Name, 5)
End If
Next tdf
End Sub
--

John W. Vinson [MVP]

Tipping my hat...

Thanks John, was still looking for mine which is on this computer SOMEWHERE!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"John W. Vinson" wrote in message
...
On Tue, 17 Feb 2009 10:21:33 -0800, Dale Fye wrote:

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go
in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't
they?

As a public service...

Public Sub RenameSQLTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "dbo_" Then
tdf.Name = Mid(tdf.Name, 5)
End If
Next tdf
End Sub
--

John W. Vinson [MVP]

John,

Looks like you've done this before. ;-)

I've got a relinking routine that accomplishes the same thing, but this is
short and to the point.

Dale


"John W. Vinson" wrote in message
...
On Tue, 17 Feb 2009 10:21:33 -0800, Dale Fye wrote:

It has been a while but I believe that during the linking process, the
linked table names will all start with "dbo_", so the user will need to go
in
and change the local name to the same name the table had when it was in
Access. But after that, the queries should generally work, shouldn't
they?

As a public service...

Public Sub RenameSQLTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "dbo_" Then
tdf.Name = Mid(tdf.Name, 5)
End If
Next tdf
End Sub
--

John W. Vinson [MVP]