Linking a SQL database to an Access database.

[Frank]

I want to create a link between my SQL server database and an access
database. I used the following code with the querry analyzer.

_________________________________________________________


USE master
GO
EXEC sp_addlinkedserver
@server = frank
@provider = Microsoft.Jet.OLEDB.4.0.6218.0
@srvproduct = Microsoft OLE DB Provider for Jet,
@datasrc = C:\Documents and Settings\Frank Smith\Desktop\test.mdb
GO

_____________________________________________________________

However, I get the follwoing error message when running the querry:

Incorrect syntax near '@provider'.

What am I doing wrong?

Assistance is greatly apprecated.

Frank

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you need quote delimiters on the parameters and commas between
each parameter:

@server = 'frank',
@provider = 'Microsoft.Jet.OLEDB.4.0.6218.0',
@srvproduct = 'Microsoft OLE DB Provider for Jet',
@datasrc = 'C:\Documents and Settings\Frank Smith\Desktop\test.mdb'

This question probably would have been better posted in

microsoft.public.sqlserver.programming

Since your question is really about SQL Server.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRCWxN4echKqOuFEgEQLgEQCg/GGB5tqtWhbxp6ua5khKhUWgUM0AoI3K
y8KNz8IxOwkgYri8V+n8bwVm
=SIh2
-----END PGP SIGNATURE-----

 

[Frank]

Thanks for comments. Tried suggestions without success. Error indicated that
I already had server named Frank.
All I want to do is copy a SQL table to an Access database using Enterprise
Manager Wizard.
This is probably too much for SQL to handle. Can not get to suggested
newsgroup.

Thanks for assistance.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can link the SQL Server table to the Access db. Just use File > Get
External Data > Link. When the file open dialog box opens select ODBC
in the File Types field on the bottom of the dialog box. Then select
the DNS of the SQL Server DB you want to connect to (or create a new DNS
to that DB). Select the table you want to copy to Access. The table
will link. Then you can use an Append (INSERT INTO) query to read the
data from the SQL'r table into an Access table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRCiaNYechKqOuFEgEQKnhQCg5+JHBc3yf7zqG7ZVOOW4gfZXhWUAnikP
ooO9fCuFENXA+N7xUy0QozCj
=9PU3
-----END PGP SIGNATURE-----