Re: Performance of ODBC
- From: "aaron.kempf@xxxxxxxxx" <aaron.kempf@xxxxxxxxx>
- Date: 26 Nov 2006 09:38:00 -0800
you will have to use SQL authentication for the most part; unless you
have a VPN connection or something
you will need to redirect the port through the firewall so that it can
hit your server.
if you have a software firewall; allow 1433/1434 through it
if you have a software firewall; forward 1433/1434 to your server
Bruce Maston wrote:
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
.
- References:
- Re: Performance of ODBC
- From: Vadim Rapp
- Re: Performance of ODBC
- From: Bruce Maston
- Re: Performance of ODBC
- Prev by Date: Re: Connection from remote computer to network SQL Server
- Next by Date: Re: Performance of ODBC
- Previous by thread: Re: Performance of ODBC
- Next by thread: Re: Performance of ODBC
- Index(es):
Relevant Pages
|
