[AccessD] Access to SQL Server over Hamachi - it WORKS

Wow... so what are you going to do next week?

The truth is that ADO plays nice with them all. I have been using ADO-OLE
since Access97 with no problems what so ever. ;-)

Moving the queries into Stored Procedures is a slow process but MS SQL
Server Migration Assistant Wizard for Access is supposed to solve all that
but I have been having some issues making it work with an Access2003 MDB.

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Sunday, January 16, 2011 7:21 PM
To: Access Developers discussion and problem solving; VBA; Sqlserver-Dba
Subject: [AccessD] Access to SQL Server over Hamachi - it WORKS

Today I stepped back and set it all up internal to my network.

Yesterday I had moved the Fe to my laptop.  Today I deleted all of my linked
tables, then I relinked 
all of the tables.  In doing so I created a new DSN where I hit the SQL
Server directly at the 
Hamachi IP address.  Relinked using this new DSN and voila, she's a wurkin,
and lightning fast over 
my internal network but forcing the traffic through Hamachi.

So I tested a bit, then went back out to the local Arby's where I was pretty
much unable to do 
anything last night (other than browse the internet).  Got right on the
connection, opened the 
Access FE and voila, she's a wurkin, and lightning fast.  You wouldn't know
you weren't directly on 
my LAN.

Access on my laptop over Hamachi, over a public network, in to my network,
directly into a Virtual 
Machine over Hamachi, running SQL Server 2008 Express.

I am so jazzed!  that leaves me with one technical barrier now, getting an
Access 2007 run-time to 
host an Access 2003 FE.

Now I need to set up a VM to run on my laptop so that I can have an
environment without Office 
installed.  Install the Access 2007 run-time and drop the Access 2003 Fe in
place.  Set up Hamachi 
on that VM and then get it all playing nice.

This will allow me to take my laptop on the road to demo the system.

We shall see.

And yes Jim, I understand I still need to cause SQL Server to do the heavy
lifting.  To this point I 
have never really used SQL Server as the BE for Access.  My biggest client
uses Access 2K and it 
doesn't really play nice with ADO.  Since I can now develop these new
databases in 2003, I can do 
things like bind the form to an ADO recordset and still have it read/write.
I will need to learn 
how to use a stored procedure were I can pass in a PK and have the SP return
a table of data already 
selected and sorted.  I.e. SQL Server doing the lifting.

The nice thing is that these are small databases so I can do things like
leave the list tables just 
linked, at least for awhile while I learn all the stuff I have never had to
do.

Next up, 2007 run-time.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com