x

Where do I make the connection to the internet DB

I am new here and new to SQL.

I understand access very well and have read all three: http://www.sqlservercentral.com/articles/upgrading/accesstosqlserverlinkingtables/1721/

And feel that I understand it. But what I cannnot find is where I make the actual reference or cannection to the sql which I have on a remote server online???

This to me seems to be the starting point that I am missing.

What I have: A sql on a shared server which I use via asp.net MISSING KNOWLKEDGE HERE<<<<<<<<<<<<<<<<<<<<< Tried to create ODBC Data Source Administrator File DSN worked with the upsizing wizard

I am using access 2000 on Vista ultimate

How do I tell the system where the sql is online and the UN and PW?

more ▼

asked Feb 08, 2010 at 01:08 PM in Default

Russ gravatar image

Russ
5 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If you go to Start > Run and type in 'odbccp32.cpl' you will get the ODBC Data Source Administrator below...

alt text

You need to set up a data source in here with your server details then in Access you right-click on the tables area and select Link Tables... which gives you a file box like so...

alt text

Select files of type ODBC Databases() and you will get a box that looks like...

alt text

Select the data source you created and click OK...

alt text

Now you will see the list of objects available on the server. Select one of them and make sure you check the save password box.

I hope this helps you Russ.

more ▼

answered Feb 09, 2010 at 07:08 AM

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

Thanks for changing the image links Matt how long is it until I can post them? I looked in the FAQ but it didn't state.
Feb 09, 2010 at 07:15 AM Ian Roke
Ian: I think you need at least 15 rep
Feb 09, 2010 at 09:52 AM Kev Riley ♦♦
Bit of a silly thing to block IMO especially with the number of guys moderating. I can't wait until I reach the threshold whatever it might be! :-)
Feb 09, 2010 at 10:08 AM Ian Roke
heh, from the title I thought this was related to sql azure
Feb 09, 2010 at 11:06 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

Do you have an Access application and need to use the linked tables manager to link to the SQL database or are you developing an ASP.net web application?

I think there is an Access wizard that lets you enter the SQL Server details and will prompt for security details in order to show you the list of tables for you to select which you want to link. Sorry I cant be more precise, I dont have Access installed and its been a while since I used it.

ASP.net applications, if you are developing in VS will have a datasource object that you create and, again, enter security details to get connected to the SQL Server objects

more ▼

answered Feb 08, 2010 at 01:19 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(comments are locked)
10|1200 characters needed characters left

I found the answer just before I recieved these answers. Both of the answers are right.

The answer is so simple, but if you have never done it beforeit seem to be more than it really is:

STEP 1 From access right click in the "Tables" window then select "link tables"

or you can do "File>Get External Data>Link Tables"

Then in the "Link" dialog by "Files of Type" select "ODBC Databasaes()"

Alternate STEP 1

Go to to "Control Panel"

Open "ODBC Data Sources"

Both of the above do the same

Step 2 - to a bunch of other steps

Click the "File Data Source" Tab

Click the "New" Button

Select "SQL Server"

Click "Advanced"

Add:

DRIVER={SQL Server}

SERVER={ip.ip.ip.ip\sqlexpress} <

DATABASE={My_Database_Name}

So that the entire contents of the box looks like above.

Click "OK"

Click "Next"

Enter a file name for the New Data Source

Click "Next"

Click "Finish"

Enter a brief description about the Data Source < if needed

Click "Next"

Select "With SQL Server authentication using login ID and password entered by user"

Click "Client Configuration"

Uncheck "Dynamically determine port"

Enter "1433" in the "Port number" box

Click "OK"

Enter the "Login ID" for your SQL Database

Enter the "Password" for your SQL Database

Click "Next"

The wait... for the next box

If is says "connection failed" you need to start again and make the changes

It should be the "Microsoft SQL Server DSN Configuration" Dialog

Click "Next"

Click "Finish"

Click " Test Data Source"

Click "OK"

Click "OK"

Click "OK"

enter the password

Click "OK"

If you did this by right clicking in the "Tables" pane ing Access simply select the SQL tables you want.

ALSO<<<<<<<<<<<<<

Check the "Save password" box, so that you don't have to enter the password everytime you open the database

Click "OK"

and DONE!!!!! Woooo Whoooo!

If you did this from ODBC Data Sources in control panel

you will need to go into Access right click in the "Tables" pane

In "File of type" box choose "ODBC Databases ()"

then fine the .DSN file you created and select it.

Ultimately you are simply creating a basic text file with the below contents:

[ODBC]

DRIVER=SQL Server

UID=myuserid

Address=205.178.152.128\sqlexpress,1433

Network=DBMSSOCN

WSID=MYWORKSTATIONor COMPUTERIDNAME

APP=Microsoft Office XP

SERVER=ip.ip.ip.ip\sqlexpress

Description=this is the description and is simply for reference purposes

Save the text file with a .dsn file extension and use it for you connection.

Once you have done this a couple of times you should be able to make your connectionsa in well under 60 seconds.

more ▼

answered Feb 10, 2010 at 11:47 AM

Russ gravatar image

Russ
5 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x49
x46

asked: Feb 08, 2010 at 01:08 PM

Seen: 1180 times

Last Updated: Aug 17, 2010 at 01:23 AM