Sql server as back end

[Nandini]

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,

 

[Danny Lesandrini]

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.

 

[Dale Fye]

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.

 

[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,

 

[Danny Lesandrini]

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.

 

[Gina Whipp]

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

 

[John W. Vinson]

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

 

[Gina Whipp]

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

 

[Dale Fye]

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]

John,

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

Just a couple of times a week lately, as I'm tweaking the structure of a 98
table (currently) SQL backend and of an Access frontend to it... <g>