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]

Re: Access to SQL Server

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" <info@businessf ormat.co.uk> wrote in message
news:8thvpvsri5 3tolu9cg8n7mdb4 7ejn54no8@4ax.c om...[color=blue]
> 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[/color]

[Arno R]

Re: Access to SQL Server

> The answer is yes; you'll have to change the name of the table in any Access[color=blue]
> 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.[/color]

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.Re fresh
For i = 0 To db.TableDefs.Co unt - 1
If Not db.TableDefs(i) .SourceTableNam e = "" Then 'ivm MSys*
If Left(db.TableDe fs(i).Name, 4) = "dbo_" Then
DoCmd.Rename Mid(db.TableDef s(i).Name, 5), acTable, db.TableDefs(i) .Name
End If
End If
Next i
db.TableDefs.Re fresh
End Function

--
Hope this helps
Arno R

[Lapchien]

Re: Access to SQL Server

Is there a utility to upsize queries?


"SA" <~fake.address@ nspm.com> wrote in message
news:bnokd1$c18 $1@ngspool-d02.news.aol.co m...[color=blue]
> Patrick:
>
> The answer is yes; you'll have to change the name of the table in any[/color]
Access[color=blue]
> based queries. Use a good find and replace utility like Rick Fisher's[/color]
Find[color=blue]
> and Replace for Access (www.rickworld.com) to save yourself oodles of[/color]
time.[color=blue]
>
> 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[/color]
functions)[color=blue]
> 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[/color]
the[color=blue]
> 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" <info@businessf ormat.co.uk> wrote in message
> news:8thvpvsri5 3tolu9cg8n7mdb4 7ejn54no8@4ax.c om...[color=green]
> > 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[/color]
>
>[/color]

[Larry Linson]

Re: Access to SQL Server

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" <arracomn_o_s_p _a_m@tiscali.nl > wrote in message
news:3f9fe98f$0 $79348$5fc3050@ dreader2.news.t iscali.nl...[color=blue][color=green]
> > The answer is yes; you'll have to change the name of the table in any[/color][/color]
Access[color=blue][color=green]
> > based queries. Use a good find and replace utility like Rick Fisher's[/color][/color]
Find[color=blue][color=green]
> > and Replace for Access (www.rickworld.com) to save yourself oodles of[/color][/color]
time.[color=blue]
>
> 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[/color]
to SQL-server.[color=blue]
> 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.Re fresh
> For i = 0 To db.TableDefs.Co unt - 1
> If Not db.TableDefs(i) .SourceTableNam e = "" Then 'ivm MSys*
> If Left(db.TableDe fs(i).Name, 4) = "dbo_" Then
> DoCmd.Rename Mid(db.TableDef s(i).Name, 5), acTable,[/color]
db.TableDefs(i) .Name[color=blue]
> End If
> End If
> Next i
> db.TableDefs.Re fresh
> End Function
>
> --
> Hope this helps
> Arno R
>
>
>[/color]

[Patrick]

Re: Access to SQL Server

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
<info@businessf ormat.co.uk> wrote:
[color=blue]
>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_tablenam e.
>
>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[/color]

[Rick Brandt]

Re: Access to SQL Server

"Patrick" <info@businessf ormat.co.uk> wrote in message
news:p4tvpvc3hs 8a264o1gbo0u3qo 9hl2u6m10@4ax.c om...[color=blue]
> 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[/color]

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