Can “Access 2007†Access Remote SQL-Server?

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Can “Access 2007†Access Remote SQL-Server?

Is it possible to access a Microsoft SQL-Server database that resides in
another firm’s location? Our Website hosting is outsourced. A small number
of SQL-Server tables are used to support this site. We want to access the
data in these tables via Access 2007 at our site.

Just recently we have set up a test system with Access 2007 getting at a
local SQL-Server database via ODBC. This works nicely. Now we would like to
connect to the remote tables that support our Website. They are located in
another state at the hosting firm’s location. We would like to connect over
the internet. There is no VPN.

If this connection is possible, where can I learn about how to set it up?

If getting at these remote SQL-Server tables is not possible with
Access-2007, are there good alternative approaches. Our budget is very
limited.

Thanks in advance for your help.
Brad
 
Brad,

Yes you can sdo that. On your control panel, there is a icon, Data Sources
(ODBC). Open it and click the System DNS tab. Click the Add button and go
through the Wizard. You will need the IP Address and login info for you
Database. Get that from your host provider. You will give it a name and
save it at some point.

In Acces click the External Data tab and the Import group select More. Then
ODBC Database. Click the Link to the data source... option. Click the
Machine Data source tab and you should see your new DNS name. Select it and
you will get a login screen to the database. After logging on, you will
get a table list. Select the tables to link and click OK.

The links will have a dbo_ prefix. You can rename the link and remove
that.

God Bless,

Mark A. Sam
 
Thank you very much for the help. Unfortunately, I am still a bit confused
on setting up the new ODBC Data Source.

My PC has XP SP3 (not sure if that makes a difference in setting this up)

I went into Control Panel / Administrative Tools / Data Source (ODBC) /
System DSN

I then clicked the add button and receive a small panel to select a driver

I then selected “SQL Serverâ€

Next I received a small panel with three input fields
Name, Description, and Server
I understand Name and Description, but I am not sure about what to enter in
the “Server†field.

Is this where the IP address goes?

If I enter something in this field, and push “next†I receive another panel
that asks for Authorization Info. It also has a button for configuration.
If I push this button, I receive another panel but it doesn’t have a spot to
enter the IP address.

Thanks again for your assistance, I really appreciate it!

Brad
 
Back
Top