SQL Server 2012 Linked Server (MS Access) - Error: 109

I am trying to create a linked server using the “microsoft office 12.0 access database engine ole db provider” as the provided to a linked database on a shared drive. The access database contains 50+ linked csv files which are updated daily through an FTP job. In the past, I was using the Jet 4.0 driver linking the directory and used with a schema.ini. Since I upgraded from SQL Server 2008 to 2012 I no longer have the option to use the Jet driver. When I test the new linked server connection the connection succeeds but when I attempt to access the tables through a query or through the object explorer window I get the error in the attachment. Any ideas on what might be wrong? Thanks experts!

Previous post has additional details:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Windows_Server_2012/Q_28237090.html
Error-109.JPG
LVL 1
HRISTeamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HRISTeamAuthor Commented:
I uninstalled and reinstalled:

AccessDatabaseEngine_x64.exe

from http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

Does that seem correct?

SELECT SERVERPROPERTY('Edition') = Standard Edition (64-bit)
0
HRISTeamAuthor Commented:
Any ideas why this won't work?
0
Jim P.Commented:
I'm just going to go back to the whole concept of linked files. Why not just import the data into the DB?

How long can it actually take? And you can program that if the file date hasn't changed don't bother.

I have a scheduled job that fires an Access DB up every morning and loads a bunch of files, puts it in the DB and uploads it to a SQL Server. It sends e-mails and everything. It takes about 15 minutes to do it all.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

HRISTeamAuthor Commented:
Eventually there will be 350 tables so it could take a while. I might consider loading all if I can't make it work. Do you have a link to a good example?
0
Jim P.Commented:
Is it the same data just different files or is it different for each table?

The coding sort of varies depending on which way your data goes.

I have about 250 files named %computername%_AppData.txt. Then I have single files that contain something like computername; IP; uptime; etc. The %computername% files take about a half second to process per file. I use the %computername% value as part of the data. The consolidated files take about 15 seconds to process.

So without knowing your data/file structure I can't give you a straight answer.
0
HRISTeamAuthor Commented:
I'll need to reload all 350 tables daily since there will be new data added to the vast majority of tables.
0
HRISTeamAuthor Commented:
Any coding examples would be appreciated.
0
HRISTeamAuthor Commented:
Anyone there???
0
Jim P.Commented:
I'll need to reload all 350 tables daily since there will be new data added to the vast majority of tables.

The question was more of do you have 350 files with individual data that has a corresponding table?

Or is it 350 files with similar data but in different files that needs to go into the same table?

Here is some old code that will import into a single predefined table.

Public Function Import_CSV_File()

Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim Wire_Number As String
Dim Gauge As Integer
Dim Color As String
Dim Length As String

Dim I As Integer

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Wires_Table")    '<-- Change to your tablename

'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\Temp\Wires.csv"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum


Line Input #FileNum, InputString     '<--- To throw away the header line
Do Until EOF(FileNum) = True
     Input #FileNum, Wire_Number, Gauge, Color, Length          'Read the data in

    With RS                                 'the input is an empty string write it
        .AddNew
        !Wire_Number = Wire_Number
        !Gauge = Gauge
        !Color = Color
        !Length = Length
        .Update
    End With
Loop

Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing

End Function

Open in new window


But I can give you code to create a table on the fly, and the rest but it takes some idea of what is needed.

Sorry I didn't get back to you earlier.
0
HRISTeamAuthor Commented:
I appreciate the code example however, I am trying to use SQL Server not Access. The VBA code you sent gets me no closer to being able to access the information from SQL Server or am I missing something. I was expecting a TSQL stored procedure of some sore. I know how to get the data into Access.
0
Jim P.Commented:
Honestly, I'm not a fan of DTS or SSIS. I've run into too much bad programming from "developers" that just has put me off and then the fix is too many days out. I have a version 45 Access DB that has successfully converted over 300 clients from an Interbase DB to be ready for the next SW. After about  version 14 most of the fixes have been corner cases from clients and not  a significant change.

My thinking is that you program an Access DB to import the data into Access and then either upload the data to the SQL Server, or have the SQL Server pull the imported data from the Access DB once it was checked.

So I think it was a case where we were talking past each other.
0
HRISTeamAuthor Commented:
I think I know what you mean, thanks for the clarification. In my case, I wrote code to link *.csv files in a given folder using a schema.ini in MS Access since SQL Server doesn't directly support linked tables (to my knowledge). My hope was then to connect SQL Server 2012 (64 bit) with my MS Access database that contains all the linked tables. I already have the Access database working (updating the linked files daily) so I was going to use it to explore tables and write queries in SQL Server. I know this is kind of the opposite of what is usually done but I thought it would be a simple solution but the linked server errors have stumped me for a week! I can make it work on a 32 bit version of SQL just not the 64 bit version. So I think we are discussing a very similar solution... I appreciate the help!
0
Jim P.Commented:
Which version of Access are you using?

Because if the Office version is 32 bit, then to create an ODBC connection to it you need to use the 32 bit version of the driver. Do  "C:\Windows\SysWOW64\odbcad32.exe" from the Run/Open Search line. Then create the ODBC call from there. If you just open the ODBC Administrator from the Control Panel then you are getting the 64 bit version which has problems connecting to 32 bit apps.
0
HRISTeamAuthor Commented:
I created the database on my laptop which is a 32 bit version of Access. The odd thing is that when I remove the 64 bit driver and install the 32 bit driver, it will not appear in the drop down as an option when I create a new linked server the way the 64 bit Access Engine does. I attempted using the 32 bit driver with "OLE DB Provider for Microsoft Directory Services" and it connects when you test the connection but it produces the attached error when I navigate the tree. I am not too familiar with setting up ODBC connections but I will mess with the ODBC administrator from the control panel. Thanks for your help Jimpen, it is the best lead I have had and I have been working on this for quite some time now!
Error7301.jpg
0
Jim P.Commented:
I wrote an article Creating DSNs for SQL Server: In depth on how to make one.Creating DSNs for SQL Server: In depth on how to make one.

That is more for getting to SQL than to an Access DB, but it might help.

I'm going to have to update it about the 64/32 bit versions at some point.
0
HRISTeamAuthor Commented:
Step by step DSN creation (contains errors)I attempted to create a System DSN using a 32 bit Access driver. I am really not sure why it won't work. I included a screen shot to document what I did, can you please see if there is anything obviously incorrect. One driver succeeds for a connection test but errors as I navigate the hierarchy and the other fails to connect. I am not even sure which driver I am supposed to be using. I appreciate your help. Thanks!!!
0
Jim P.Commented:
Is Y: a mapped drive? Then it won't work. The SQL Server service can't see Y:, that is dependent on you login.  Try it as the UNC  "\\ServerName\ShareName". And make sure whatever account is running the SQL Service is added in the permission for both the share and the actual folder.

And you may have to restart SQL if you have to change those permissions.
0
HRISTeamAuthor Commented:
Thanks, will give it a try with a UNC path.
0
HRISTeamAuthor Commented:
I attempted to set up a new odbc connection with no success. Please review the attached screenshot to see if you can help me figure out what I am doing wrong. Thanks
MS-Access-UNC-error.jpg
0
Jim P.Commented:
Do you have the same version of Access on the server?

Can you open the Access DB on the server via the UNC?

Can you log into the server using the same account that the SQL Server uses and get to Access DB via the UNC?

Which ODBC Administrator did you use?

I want to get you through this, so please don't lose hope.
0
HRISTeamAuthor Commented:
Thanks for your patience and perseverance, I know this seems like an easy task but I am really struggling! Let me just say that I am not a DBA, I am much better coding and I am trying to get this set up with minimal support. My knowledge of configuring this is limited to articles I have read on the internet which don't appear to address my specific issue.

For a little more background, I am running the SQL Service under the sa.SQLuser which is a member of the AD group which gives permission to the shared directory where the Access database resides. Additionally when I create the linked server I am trying to impersonating the sa.SQLuser (even though I also have access to the share). I am using the option "Be made using the login's current security context" with the saSQL user included with a check in the impersonate check box (screenshot attached). Is that actually using the sa.SQLuser? That being said I will do my best to answer your questions.

The MS Access application is not installed on the SQL Server box. I installed AccessDatabaseEngine.exe from http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255. My understanding is that you can't install a 32 bit MS Office Application on a 64 bit server and I thought the Access Engine would function in place of the actual application. My ultimate goal is to use the databases I built on my 32 bit pc. If the Access Engine is not sufficient then what do I need to install on the server?

When I created the ODBC link, I remoted to the SQL box using my AD account (not the sa.sqluser). I browsed out to the folder and selected the appropriate directory. I did have the DBA login using the sa.sqluser and confirmed that the directory was accessible through windows explorer. I have requested the sa.sqluser account to no avail but if I know exactly what needs to be done I can have the DBA type the password for me. Unfortunately I don't know how to open the database using the UNC. Can try if you can point me in the right direction. Additionally, I don't see a "test connection" function anywhere when I create the ODBC connection so I really have no idea if I actually set it up correctly.
security.jpg
0
Jim P.Commented:
My most advanced SQL is 2008 and same with server. So bear with me as I haven't actually dealt with this all before, but is similar to issues I have dealt with in the past.

If you can login to the server desktop as anything see if you can bring up Computer Management (compmgmt.msc) it should bring up the Computer Management console. From there you have a list of services that will conclude what account the SQL Service is using.

The SA (Sys Admin) account uses the same account that the SQL Service is running under. And any SQL authentication is run under that same service account. Where if it is a windows authentication (MyDomain\Username) there is an undefined combo of authority.

So now that that is split out make sure that the SQL service has permissions to the SQL directory?

Then the DBA may have to restart the SQL service to get the permissions to take.

So the sa.sqluser user account needs permissions.

We may turn you into a production DBA if we keep working this over. ;-)
0
HRISTeamAuthor Commented:
The sa.sqluser is running the SQL Server (MSSQLSERVER) & SQL Server Agent (MSSQLSERVER). Both startup types are automatic. I stopped and restarted the services and I am still getting the same error. The sa.sqluser is an admin on the machine and has full permissions. I even tried to put the db on the C and D drive to see if that help and it was no different.

The SQL Server Browser is running under Local Service
The SQL Server VSS Writer is running under Local System

I should not need to reboot if I stopped and restarted all the SQL services, right? Or should I just do it for good measures?
0
Jim P.Commented:
So if you go to the machine where your Access DB is, do you see the "sa.sqluser" listed as permitted to access that folder?  And then the share as well?

I'm going to bed soon. So if you don't get a quick reply -- don't worry.
0
HRISTeamAuthor Commented:
I don't see sa.sqluser but I do see the AD developer group which sa.sqluser is a member of.
0
Jim P.Commented:
Just for grins, try adding the account directly to folder and the share. Then see what happens.
0
HRISTeamAuthor Commented:
My organization doesn't grant access to individual users everything goes through AD groups. However I do know for a fact the the sa.sqluser has access to the directory since I did have the dba confirm by logging in and navigating to the location. Additionally, I can get the connection to pass the initial test, it is only when I navigate to something like "Views" where I get the error or when I try to query a table using the linked server.
0
HRISTeamAuthor Commented:
I did get the sa.sqluser login credentials and verified the permissions are correct to the share by remoting to the computer under the sa.sqluser.
0
Jim P.Commented:
I should not need to reboot if I stopped and restarted all the SQL services, right? Or should I just do it for good measures?

You shouldn't need a reboot.

You can install a 32 bit version of Office on a 64 bit machine. It will automatically redirect to the C:\Program Files (x86) directory. Can you try loading the same version on the server?
0
HRISTeamAuthor Commented:
Can see about that on Monday. In the meanwhile, I did find out that I was not using the driver in SSMS. The correct driver won't configure wo errors. The one that passed the connection test was the AD driver.
0
HRISTeamAuthor Commented:
I spent over week messing with this issue with no real help troubleshooting my Linked Server issue so I made some basic documentation in the event someone else has the same problem.  See document at the link below:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28248137.html#a39555782
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HRISTeamAuthor Commented:
Answered the posted question unlike all other responses.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.