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?
If you go to
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...
Select files of type ODBC Databases() and you will get a box that looks like...
Select the data source you created and click OK...
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.
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
answered Feb 08 '10 at 01:19 PM
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"
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"
So that the entire contents of the box looks like above.
Enter a file name for the New Data Source
Enter a brief description about the Data Source < if needed
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
Enter the "Login ID" for your SQL Database
Enter the "Password" for your SQL Database
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 " Test Data Source"
enter the password
If you did this by right clicking in the "Tables" pane ing Access simply select the SQL tables you want.
Check the "Save password" box, so that you don't have to enter the password everytime you open the database
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:
APP=Microsoft Office XP
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.
answered Feb 10 '10 at 11:47 AM