Getting Access data into SQL server

Question

Post reply

Getting Access data into SQL server

Steve Sziklai

Valued Member

Points: 53
More actions
September 16, 2005 at 12:09 pm

#165486

I am trying to get some data from my application (in an Access DB) into a clients hosted web site. They are running SQL server with a toolset that has an import Access table function that grabs my data and puts it into an SQL table.
I want to be able to write an Stored procedure to do this task so the import / update of other SQL tables can be automated. I tried the bulk import route (via an ASCII file) but permission is denied to my user (and apparently can't be granted as it would give me access to the entire server).
I then tried Opendatasource as follows:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="/www/Templates/webupd.mdb"')...web_item_upd

When I execute this I get:

[Microsoft][ODBC SQL Server Driver][SQL Server]Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
I don't know what this means and when I passed it on to the tech guys at the ISP, they also did not give me anything that helped.
Any clorification of this specific problem or suggestions on how I could import an access table into the sql server using a stored procedure would be greatly appreciated.
Thanks,
Steve

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 1

What sql server version are they running and what is their service pack?

It might be a bug that was resolved in sql server 2000 sp 3

more info:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;328569

Steve Sziklai Valued Member Points: 53 More actions · Answer 2

The sql version is:

Microsoft SQL Server 2000 - 8.00.2039

(Intel X86) May 3 2005 23:18:38

on Windows NT 5.0 (Build 2195: Service Pack 4)

____________________________________________________________

I also found the kb328569 article that you mentioned and have sent it on to the ISP that is running the site. So far they have not addressed that specific issue. It does seem to be pretty much the exact problem that I am having.

I am a bit out of my element working with the specific interface and with limited SQL Server experience. There seems to be some confusion with the people that I am talking with about whether the connection can be done via an sql statement from SQL Query or if I need the 'enterprise' module and DTS?? If someone could clear this up it would be really helpful.

Thanks in advance.

Steve

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 3

Looks they have service pack 4 of sql server where the issue should be resolved.

If adhoc queries of your access databases isn't allowed, you can still refer to them if you set up a linked server. (no need for the enterprise module or DTS)

See the books online (bol) for more info.

OLE DB Provider for Jet

The Microsoft® OLE DB Provider for Jet provides an OLE DB interface to Microsoft Access databases, and allows Microsoft SQL Server™ 2000 distributed queries to query Access databases.

To create a linked server to access an Access database

Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.
For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the C:\Mydata directory, execute:
```
sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',     'c:\mydata\Nwind.mdb'
```
To access an unsecured Access database, SQL Server logins attempting to access an Access database should have a login mapping defined to the username Admin with no password.
This example enables access for the local user Joe to the linked server named Nwind.
```
sp_addlinkedsrvlogin 'Nwind', false, 'Joe', 'Admin', NULL
```
To access a secured Access database, configure the registry (using the Registry Editor) to use the correct Workgroup Information file used by Access. Use the Registry Editor to add the full path name of the Workgroup Information file used by Access to this registry entry:
```
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
```
After the registry entry is configured, use sp_addlinkedsrvlogin to create login mappings from local logins to Access logins:
```
sp_addlinkedsrvlogin 'Nwind', false, 'Joe',                        'AccessUser', 'AccessPwd'
```

Access databases do not have catalog and schema names. Therefore, tables in an Access-based linked server can be referenced in distributed queries using a four-part name of the form linked_server...table_name.

This example retrieves all rows from the Employees table in the linked server named Nwind.

SELECT * FROM Nwind...Employees

Steve Sziklai Valued Member Points: 53 More actions · Answer 4

Thanks for the detailed info. It confirms the things that I have been trying, but am so far hitting a brick wall with. When I try to do the sp_addlinkedsrvlogin I get 'User does not have permission to perform this action'.

As I am trying to do work with the system through a remote client UI I cannot get at the registry.

Can you tell me if I would be able to use my original SQL

['SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="/www/Templates/webupd.mdb"')...web_item_upd]

( no linked server) if the AD HOC queries were permitted?

Thanks and regards,

Steve

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 5

If adhoc queries would be permitted, your original select will most likely work.

I personally haven't any experience querying access tables through sql server.