link to express table in access 2003

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

link to express table in access 2003

Author

Topic

solar
Starting Member

5 Posts

Posted - 2006-01-29 : 17:47:11

How can make link to a table stored in an sql express 2005 database in access 2003 ? the classic method doesen't work. i tried to use odbc databases as file type to link, then build a data source using SQL native clint driver, but in the combo for default database i don't have the database created before, only master,msdb, model, tempdb. If i try to use the attach database filename text box i receive an error "the datadase entered is not valid". Any suggestions ?

Thx

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-30 : 01:02:30

Hello Solar
welcome to SQLTeam.

Open access, and in the table menu, right click anywhere and choose link table.
1. A dailog box would appear
2. In the "file of types" scroll down and choose ODBC connection source
3. Create your ODBC connection and ....

...presto you are on your way

afrika

solar
Starting Member

5 Posts

Posted - 2006-01-31 : 06:28:55

You didn't read what i wrote. I already tried what you said.
So i'm waiting for another sugesstions.
Thx

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2006-01-31 : 13:39:47

I've followed the steps you list and they work as expected. Are you sure the database you're trying to link to is Attached to the server instance you're pointing to? Here's how how to check using SQLCmd.

1. Launch a command window.
2. Type

SQLCmd -E -S .\SQLEXPRESS

Subsitute the instance name you're trying to use in Access.
3. Once you get the SQLCmd prompt, type the following:

1> Use Master
2> Go
1> SELECT Name FROM sys.databases
2> Go

4. You should get a list of the databases currently attached to the instance.

If you're database is listed, double check that you've picked the correct server instance name when setting up your DSN. If your database is not listed, you will need to attach it using the CREATE DATABASE command. You can find information about how to use CREATE DATABASE in Books Online or on MSDN2 (http://msdn2.microsoft.com).

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

solar
Starting Member

5 Posts

Posted - 2006-02-01 : 12:50:59

OK Mike thanks for your time to answer to my question.
I made what you've said and here are the answers:

1) the SQL express database(s) from i've tried to link tables in access 2003 is listed in attached databases
2) database was created with access wizard for project using new data (sql server instance was chosed correct, i used trusted connection), no errors were reported after database creation.
3) because i cannot create tables, etc (cannot save design for those objects), i used c# 2005 express to create them (here i select database previously created in access by physical location of the database file). btw the c# express integration with sql express is very fine.
4) after i created the tables in C# express i tried to link them in access to use it for some reports (access is better than C# express in this matter).
5) I opened the access project and i saw that the connection is closed; if i tried to reconnect i get an error message from access: ... cannot open database .. requested by login failed, etc
I stopped/ started express server, tried to connect again from access project to that database this time i didn't receive any errors but in in the combo for "select database from the server" that database don't appear. damn

6) i tried to link those tables in an access database (not a project) but i cannot find the database name in the change the default database to combo. If I tried to use the attach database file name text box to set the database name i get an error "the database entered is not valid".

i say "no way to access a database created with access project wizard !". So i used sqlcmd console and i create one from the scratch (create database ...).

Surprise - this time the database name appears in the combo both for project and simple database. I tried to open the connection to the same database in C# express while i have the access database with those tables linked and i received an error "login failed for user...." I closed access database and i tried again this time i connected succesfully.

if i remember well in previous version of free sql server engine MSDE i can open an project connected to a msde database or a access database with tables linked to a msde database, and in the same time to work in visual studio 2003 and to open a connection to the same database in database explorer. Is this a limitation in express or what ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources