Problems and Solutions when using access to import MSSQL

1. Run

Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure

(Establish a connection)

Server: Message 15247, level 16, status 1, process sp_configure, row 170
You are not authorized to perform this operation.
Server: Message 5812, Level 14, status 1, Row 1
You are not authorized to run the reconfigure statement.
Server: Message 15123, level 16, status 1, process sp_configure, row 79
The configuration option 'ad hoc distributed querys' does not exist or may be an advanced option.

The SQL Server 2000 prompt is:
Ole db is deniedProgramSpecial Access to 'Microsoft. Jet. oledb.4.0. You must use the linked server to access the provided
Solution: If your account has insufficient permissions, you need SQL Server users to use the systemadministrators "server role ". Add your account to the SysAdmin role

2. Access to import MSSQL to automatically increase Columns

Cause: When access data is inserted into the table of an existing MSSQL database

Solution: import data columns can be embodied.

Example:

Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure

(Establish a connection)

Insert into DBO. ex_papers (professionid, examinationid, proid, course, paperurl, papertype, paperkeywords, papercontent, downcount,
Addtime, adduser, addip, lastupdatetime, lastupdateuser, checkstate, checkuser, checktime, checkip)
Select a. professionid, A. examinationid, A. proid, course, A. paperurl, A. papertype, A. paperkeywords, A. papercontent, A. downcount,
A. addtime, A. adduser, A. addip, A. lastupdatetime, A. lastupdateuser, A. checkstate, A. checkuser, A. checktime, A. checkip from
OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "F: \ website document \ temp \ access \ spiderresult. mdb"; user id = admin; Password =; ')... [content] as

 

3.The Select permission on the object 'xxx' (Database 'xxx', architecture 'dbo') is denied.

Solution:

Database (xxxxxx database) → Security → architecture → DBO (attribute) → permission → add → [public] → Grant

Delete, execute, insert, select, update

4. when you connect to other sub-databases in the LAN, an error occurs when you execute the above statements through the connected database (without using the local database) (the specific error cannot be remembered, and it seems to be a path error, PATH is the file path on the local machine)

My solution is to copy the file to the database sub-host and use the sub-host connected to the database for execution. I wonder if there is any better solution.