Re: Performance of ODBC

Hi Vadim,

Thank you for your reply. I've delayed responding to try out some of these
ideas. I've now created my own server with SQL Server 2005 Express, and I've
gotten this going on my network at the office. As such, I have two XP
computers with Access front end, and I've hooked up my older computer (with
W2K pro) where I have the SQL Server and my database tables.

I encountered some problems due to my stupidity. The more interesting
glitch concerns the Upsizing Wizard.

A bit of history here. I originally set up my ODBC connection via
Settings/Administrative Tools/ODBC and the User DSN tab. The upsizing wizard
in Access 2K worked perfectly. About a year ago, the Upsizing Wizard crashed
and didn't work anymore. I eventually learned (from tech support at the ISP)
that I needed to set up the connection with System DSN. I did this, and the
problem was solved. I can't clearly remember the reason I needed to do this
-- I think is was some Windows or Office update that lead to this.

When I used the upsizing wizard to my own server yesterday, the wizard again
crashed. I figured out that I needed to go back to a connection to
[servername]\SQLEXPRESS via User DSN. I thought I was golden until I tried
to open the app. I was getting error messages that turned out to be because
any Yes/No Access column is a Bit column in SQL. As such, whereas Access
sees Yes/No as -1/0, SQL sees this as +1/0. Amazingly (to me anyway), when
you view the data in a table via SSMS, you see the +1's. When you view the
same table via Access, you see the -1's. I corrected my problem by changing
the VBA code in Access, and I've now got it going.

As far a speed, the connection is now virtually instanteous. Thus, I've
concluded my speed problem was either in the ISP's server, or one of the
other situations that you posed (and that I've not totally deciphered yet).

Anyway, the next question is what the speed will be when I use my home
computer with the Access front end to connect to the SQL server 2005 Exp. at
the office.

This is really my next question, and maybe the end of a very long entry is
not the best place for it. How to I reach the server at my office from home?
I have a fixed IP address at the office (but not at home where I'm simply on
RoadRunner). I can't figure out how to type something in the boxes for the
ODBC connection that gets anything to happen. There is lots of info on
enabling TCP/IP to connect via a LAN (along with using SQL authentication),
but nothing on what the connection string looks like from a remote site.

Bruce Maston

"Vadim Rapp" wrote:

Most likely this is a problem with your or your ISP network configuration,
most likely with DNS. I just tried to connect from home computer to sql
server running at my work computer and published on the firewall; both
Access through odbc and vb6 program were able to connect and extract the
data practically instantly..

Did you try tracert to your server?

Also, if you close your Access and then reopen and reaccess the server data,
is it still slow? perhaps sql server is autostarted when you connect first
time, or maybe the database is autoclosed and needs to reopen?

Vadim Rapp



.

Relevant Pages

  • Re: accessing data mining model via web/PDA
    ... Is your OPENROWSET clause connecting to a SQL Server 2000 database? ... Is your SQL Server instance running on the same machine as your Analysis ... i need some> help for the connection string, let say to access the> mining model in the AS. ...
    (microsoft.public.sqlserver.datamining)
  • Re: Performance of ODBC
    ... I've now created my own server with SQL Server 2005 Express, ... glitch concerns the Upsizing Wizard. ... that I needed to set up the connection with System DSN. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: VB Express SQL Express Remote Connection
    ... Yes I can connect using the SQL Server Management Studio Express Tool. ... I changed my connection to exactly what you specified and it connected. ... I can connect and create databases on the Remote Server ...
    (microsoft.public.dotnet.languages.vb)
  • .Net Scalability problem
    ... I finished a Great project using .Net and SQL Server and .Net Mobile ... Million concurrent users ... So I think that the MTC generate concurrent connection and per ...
    (microsoft.public.sqlserver.connect)
  • Re: Communication Link Failure Error
    ... On disconnection from the network, in my code I changed the connection ... string to use SQLOLEDB and sent a SQL query to SQL server which failed as ... unplug the network cable from the client machine and put the cable ...
    (microsoft.public.data.ado)