Link Access 2007 Table to Sql 2005

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

SSIS and Import/Export (2005)

Link Access 2007 Table to Sql 2005

Author

Topic

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-11 : 08:03:15

Does anyone have a good (reasonably easy to understand) link on how to do this. I see plenty about link to Access but can't find a good article on Linking from Access to sql.

FYI - I have a Form in Access populated by users, and the results populate a table. I want to use the data in this table in sql.

rajeshweta
Starting Member

5 Posts

Posted - 2011-07-11 : 17:22:42

i don't have link but can guide you how to do.
get the ACCESS datasource and use data conversion as middle layer to compare and rectify datatype used and then get it into SQL datasource or OLE DB datasource

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-12 : 02:51:48

Are you looking for a way to query data which is currently in MS Access from SQL Server, perhaps using it in some sort of join with other tables in your SQL Server database? In that case, you need to setup something called a Linked Server on your SQL Server. It is quite simple, but do read up on the syntax and various options you have. Here's a sample for connecting to an Access database:

EXEC sp_addlinkedserver @server = ‘MyAccessDB’, @provider = ‘Microsoft.Jet.OLEDB.4.0', @srvproduct = ‘OLE DB Provider for Jet’, @datasrc = ‘C:\MSOffice\Access\Samples\Northwind.mdb’

You can then query the table directly using a four-part naming convention, for example: SELECT Col1, Col2 FROM [MyAccessDB]...TableName

Here's a good tutorial: http://www.sswug.org/articles/viewarticle.aspx?id=44218

OS

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-12 : 06:21:58

Thanks, how can I test the Datasource is connected, as I'm getting an error when I try and create the Linked Server and I think this is because it's not finding the database.

/****** Object: LinkedServer [EVENT BACKGROUND] Script Date: 07/12/2011 11:17:59 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'EVENT BACKGROUND', @srvproduct=N'Microsoft Office Access 2007 Database', @provider=N'MSDASQL', @datasrc=N'LinkedAcces'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EVENT BACKGROUND',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EVENT BACKGROUND', @optname=N'use remote collation', @optvalue=N'true'

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-12 : 06:40:44

I am sorry I should have checked the link correctly before i recommended it you, the article actually shows you to create a new ODBC DSN, which is unnecessary. You should be able to connect directly to your Access database without having to use ODBC. Also, I would recommend avoiding spaces in your server name where possible.

Also, don't forget that if you are trying to link an mdb file, the file needs to be sitting on the same server as SQL Server...don't try to link to one that's sitting on your own machine and wonder why SQL Server can't access it! I do suppose there's a way to link to an MDB that's on a network share but I don't know the implications of that on authentication, etc. Here's one that worked for me (both the mdb file and SQL Server are local on my machine):

EXEC master.dbo.sp_addlinkedserver @server = N'MYACCESSDB', @srvproduct=N'MSACCESS', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\Program Files\Microsoft Office\Office12\1033\dbsample.mdb'

OS

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-07-17 : 09:19:18

Thanks, I'm off for a week, so I'll have a play when I get back.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-17 : 05:30:13

Many thanks, that appears to have run sucessfully:

EXEC [DS Reporting Database].dbo.sp_addlinkedserver @server = N'\\ukcrow0034\shares', @srvproduct=N'MSACCESS', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'\\ukcrow0034\shares\NIM UK - COMMERCIAL DECISION SUPPORT\CRM Administration\Promotion Evaluation\Project\Event Detail.accdb'

I have no idea what I've actually done though.

What I have is a Form being completed by our Sales guys, which is populating a table in Sql. What I'd ideally like to do is have this feeding into sql, as the database is struggling for size.

What's the best approach?

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-17 : 05:58:19

Grr sorry let me re write that.

What I have is an Access 2007 Form being completed by our Sales guys, which is populating a table in Access 2007.

What I'd ideally like to do is have this table linked into sql, as the database is struggling for size.

What's the best approach?

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-17 : 21:49:49

Sorry to repost but I've been searching and can't find a good article on this.

I want to keep the Access 2007 Form Front end but want the data storage to be in my sql 2005 database.

Anyone have a reasonably easy to read article on the best way to acheove this?

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-18 : 04:59:58

From another site, is this the right approach?

How can Access 2007 be used as a Front End for an SQL 2005 Database. Access
2003 and older has the options of create new project which created an .adp
file. This Access .adp file could create a new database, change existing
data, change the design of an existing sql 2000 database. Access 2003 could
not change the design of SQL 2005 DB.

Is there a comparable feature in Access 2007 where new databases can be
added to an SQL 2005 instance, or change data and design of an existing
database in an instance of SQL 2005?

Answer:

when creating a new database, click the folder icon next to the filename. it
gives you options of where to save the database, and what type of database to
create. select access project. I think it will allow you to change the design
of the database, but I haven't tried it. let me know if it works.

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-26 : 06:22:14

Sorry but I'm still stuck on this. I tried changing the Access Database to a Project but then couldn't make changes directly in Access via the Form

So I tried the Above:

EXEC [DS Reporting Database].dbo.sp_addlinkedserver @server = N'PIRELLI', @srvproduct=N'MSACCESS', @provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=N'S:\NIM UK - COMMERCIAL DECISION SUPPORT\CRM Administration\Promotion Evaluation\Project\EventDetail.accdb'

EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'PIRELLI',
@useself = N'TRUE',
@locallogin = NULL,
@rmtuser = N'PIRELLI',
@rmtpassword = ''
GO

EXEC sp_tables_ex N'PIRELLI'
GO

SELECT * FROM [PIRELLI]...[CRM Detail]

But Am getting the Error Message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PIRELLI" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PIRELLI".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PIRELLI" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PIRELLI".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources