Re: Sql server as back end

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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



.

Relevant Pages

  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... Access ships with MSDE.. ... >SQL Server Books Online (again-- Access ships with freeware SQL Server ... better ways to achieve their results through queries. ...
    (microsoft.public.excel)
  • Re: Official Status of SQLServer 2005 ADP
    ... solution might be to use ADP. ... With MDB and Linked tables, the only ways of accelerating things are the use ... of Views and the cumbersome use of SQL passthrough queries. ... > SQL Server, and carry on using Access like I aways had. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Server 2000 and Latching problem
    ... While the way we indexed our tables and the way we wrote the queries etc ... "chances are you are getting latches because you are ... query on the results the query .. ... SQL Server 2000 and Latching problem ...
    (microsoft.public.sqlserver.connect)
  • Re: Sql server as back end
    ... There is an option in the Tools menu, Database Utilities, for Upsize to SQL Server. ... But after that, the queries should generally work, shouldn't they? ...
    (microsoft.public.access.tablesdbdesign)
  • Re: SQL Server (?) performance issues
    ... it's usually that those queries are being blocked by something else. ... Columnist, SQL Server Professional ... exact counters are being watched, but we are being told that all the ... all is fine and there is plenty of spare capacity. ...
    (microsoft.public.sqlserver.server)