Access to SQL Server

[Patrick]

Not having dabbled with this before I would appreciate some help:

I have installed SBS and SQL Server on a machine and used the wizard
to import a BE file with about 180 tables successfully.

I have created a System DSN and connected to the SQL Server from
another PC and linked the tables, the tables all appear as
dbo_tablename.

Does this mean that I would have to go through all my queries and code
in the FE and change the table name references?

Regards
Patrick

[SA]

Patrick:

The answer is yes; you'll have to change the name of the table in any Access
based queries. Use a good find and replace utility like Rick Fisher's Find
and Replace for Access (www.rickworld.com) to save yourself oodles of time.

However, to get the full benefit of the SQL Server, you should also upsize
your FE's queries (so long as they don't contain user defined VBA functions)
to the server as well and then use pass through queries in the FE to fetch
the data from the Server's newly created View or SP. This avoids using the
jet engine and uses the SQL Server engine to pull all the data, reducing
network traffic and enhancing speed substantially.

--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

"Patrick" <in**@businessformat.co.uk> wrote in message
news:8t********************************@4ax.com...

Not having dabbled with this before I would appreciate some help:

I have installed SBS and SQL Server on a machine and used the wizard
to import a BE file with about 180 tables successfully.

I have created a System DSN and connected to the SQL Server from
another PC and linked the tables, the tables all appear as
dbo_tablename.

Does this mean that I would have to go through all my queries and code
in the FE and change the table name references?

Regards
Patrick

[Arno R]

> The answer is yes; you'll have to change the name of the table in any Access

based queries. Use a good find and replace utility like Rick Fisher's Find
and Replace for Access (www.rickworld.com) to save yourself oodles of time.

Hi Steve and Patrick,

There is also a possibility to just rename the linked tables.
I had the same problem with a complicated app when the backend was upsized to SQL-server.
I made a function (so you can call this in a macro) to do so (DAO-code)

Function RenameODBC()
Dim db as DAO.Database
Dim i As Integer
Set db = CurrentDb
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If Not db.TableDefs(i).SourceTableName = "" Then 'ivm MSys*
If Left(db.TableDefs(i).Name, 4) = "dbo_" Then
DoCmd.Rename Mid(db.TableDefs(i).Name, 5), acTable, db.TableDefs(i).Name
End If
End If
Next i
db.TableDefs.Refresh
End Function

--
Hope this helps
Arno R

[Lapchien]

Is there a utility to upsize queries?
"SA" <~f***********@nspm.com> wrote in message
news:bn**********@ngspool-d02.news.aol.com...

Patrick:

The answer is yes; you'll have to change the name of the table in any Access based queries. Use a good find and replace utility like Rick Fisher's Find and Replace for Access (www.rickworld.com) to save yourself oodles of time.
However, to get the full benefit of the SQL Server, you should also upsize
your FE's queries (so long as they don't contain user defined VBA functions) to the server as well and then use pass through queries in the FE to fetch
the data from the Server's newly created View or SP. This avoids using the jet engine and uses the SQL Server engine to pull all the data, reducing
network traffic and enhancing speed substantially.

--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

"Patrick" <in**@businessformat.co.uk> wrote in message
news:8t********************************@4ax.com...

Not having dabbled with this before I would appreciate some help:

I have installed SBS and SQL Server on a machine and used the wizard
to import a BE file with about 180 tables successfully.

I have created a System DSN and connected to the SQL Server from
another PC and linked the tables, the tables all appear as
dbo_tablename.

Does this mean that I would have to go through all my queries and code
in the FE and change the table name references?

Regards
Patrick

[Larry Linson]

I've used a similar approach, but you can also rename the TableDefs
manually, if there are not too many.

Some, however, think the "dbo_" serves nicely to distinguish between server
tables and local (usually "lookup") tables. If that's not a consideration,
then the "dbo_" prefix is contentless and can be dispensed with. In one
client's application, all the local tables were prefixed with "tlkp" because
of the project's naming standard, so any table without "tlkp" was, by
convention, a server table.

Larry Linson
Microsoft Access MVP

"Arno R" <ar****************@tiscali.nl> wrote in message
news:3f**********************@dreader2.news.tiscal i.nl...

The answer is yes; you'll have to change the name of the table in any Access based queries. Use a good find and replace utility like Rick Fisher's Find and Replace for Access (www.rickworld.com) to save yourself oodles of

time.
Hi Steve and Patrick,

There is also a possibility to just rename the linked tables.
I had the same problem with a complicated app when the backend was upsized to SQL-server. I made a function (so you can call this in a macro) to do so (DAO-code)

Function RenameODBC()
Dim db as DAO.Database
Dim i As Integer
Set db = CurrentDb
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If Not db.TableDefs(i).SourceTableName = "" Then 'ivm MSys*
If Left(db.TableDefs(i).Name, 4) = "dbo_" Then
DoCmd.Rename Mid(db.TableDefs(i).Name, 5), acTable, db.TableDefs(i).Name End If
End If
Next i
db.TableDefs.Refresh
End Function

--
Hope this helps
Arno R

[Patrick]

I have just read Arnos reply, I may try that, it was too obvious, I
should of thought of that myself.

Is there any collective wisdom out there as to the difference in speed
between SQL Server BE with Access FE and Access FE/BE with Terminal
Services when talking about 5 to 10 users.
Patrick

On Wed, 29 Oct 2003 14:05:07 +0000, Patrick
<in**@businessformat.co.uk> wrote:

Not having dabbled with this before I would appreciate some help:

I have installed SBS and SQL Server on a machine and used the wizard
to import a BE file with about 180 tables successfully.

I have created a System DSN and connected to the SQL Server from
another PC and linked the tables, the tables all appear as
dbo_tablename.

Does this mean that I would have to go through all my queries and code
in the FE and change the table name references?

Regards
Patrick

[Rick Brandt]

"Patrick" <in**@businessformat.co.uk> wrote in message
news:p4********************************@4ax.com...

I have just read Arnos reply, I may try that, it was too obvious, I
should of thought of that myself.

Is there any collective wisdom out there as to the difference in speed
between SQL Server BE with Access FE and Access FE/BE with Terminal
Services when talking about 5 to 10 users.
Patrick

Too broad of a question. I would say that speed should seldom (if ever) be
the overriding issue when moving from Access to a server-based product.
Certain things might be faster after the change, but I wouldn't hang my hat
on that one possibility. Reliability, concurrency, security, scalability,
etc., are more important reasons to go to a server engine.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com