Re: Sql server as back end
- From: "Danny Lesandrini" <dlesandrini@xxxxxxxxxxx>
- Date: Tue, 17 Feb 2009 13:38:08 -0700
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
dlesandrini@xxxxxxxxxxx
www.amazecreations.com
"Nandini" <Nandini@xxxxxxxxxxxxxxxxxxxxxxxxx> 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/datafast/downloads/DataFastUtility.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
dlesandrini@xxxxxxxxxxx
www.amazecreations.com
"Nandini" <Nandini@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
.
- References:
- Sql server as back end
- From: Nandini
- Re: Sql server as back end
- From: Danny Lesandrini
- Re: Sql server as back end
- From: Dale Fye
- Re: Sql server as back end
- From: Nandini
- Sql server as back end
- Prev by Date: Re: Sql server as back end
- Next by Date: "Property value is too large."
- Previous by thread: Re: Sql server as back end
- Next by thread: Re: Sql server as back end
- Index(es):
Relevant Pages
|
