Improve company productivity with a Business Account.Sign Up

x
?
Solved

Pull Records From SQL and Access db

Posted on 2014-01-28
3
Medium Priority
?
279 Views
Last Modified: 2014-02-25
Anyone know if you can retrieve 1 record set from Access (2007) and SQL db in the same query string?

The data was in 2 Access databases.  Now slowly they are being transferred to SQL.  
Now 1 is in Access and 1 in SQL.

Here's the string I was using (it works):  
(thisSaveServer is a string with the location of the employees db on a different server.)

strSQL = "SELECT docPermissions.empNumber, (TRIM(LName) + ', ' + TRIM(FName)) as FullName "
strSQL = strSQL + "FROM [MS Access;DATABASE=" & thisSaveServer & "\App_Data\Employee.mdb].[Employees] AS employees "
strSQL = strSQL + "INNER JOIN docPermissions ON employees.EmployeeNumber = docPermissions.empNumber "
strSQL = strSQL + "GROUP BY employees.LName, docPermissions.empNumber, (TRIM(LName) + ', ' + TRIM(FName)) "
strSQL = strSQL + "ORDER BY employees.LName "

I tried to switch it around like this:

strSQL = "SELECT docPermissions.empNumber, (RTRIM(LName) + ', ' + RTRIM(FName)) as FullName "
strSQL = strSQL + "FROM HR_Employees  "
strSQL = strSQL + "INNER JOIN [MS Access;DATABASE=" & thisSaveServer & "\App_Data\documentLibrary.mdb].[docPermissions] AS docPermissions "
strSQL = strSQL + "ON HR_Employees.EmployeeNumber = docPermissions.empNumber "
strSQL = strSQL + "GROUP BY HR_Employees.LName, docPermissions.empNumber, (RTRIM(LName) + ', ' + RTRIM(FName)) "
strSQL = strSQL + "ORDER BY HR_Employees.LName "

The program calls the code below to get the data  
(This is part of a class that works for other SQL calls)

Public Shared Function GetDataReaderIntranetSQL(ByVal strSQL As String) As IEnumerable

        Dim cmd As SqlCommand
        cmd = New SqlCommand(strSQL, New SqlClient.SqlConnection(GetConnectionString))
        cmd.Connection.Open()
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
        cmd.Connection.Close()

End Function

The error comes up as:
Invalid object name 'MS Access;DATABASE=C:\inetpub\wwwroot\I4\App_Data\documentLibrary.mdb.docPermissions'.

Any ideas?
0
Comment
Question by:jshesek
3 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 39815733
Open up the Access app, and create a linked table to your SQL Server table.

Then create a query that uses both the local Access and Access linked-to-SQL Server table.
0
 
LVL 2

Author Comment

by:jshesek
ID: 39815909
Ok tried it out and it works from the actual Access db.  I can run the query and it pulls back the info I'm looking for.  
But it's a web app.  So it's going thru IIS.  

Coming up with this error:

ODBC--connection to 'EmployeeSQL' failed.

When I created the connection, I entered a SQL account name & password that has rites to it.  
But the ODBC System DSN Warning came up:
You are logged on with non-Administrative privileges.  System DSNs could not be created or modified.
0
 
LVL 86

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39816358
You'd have to create that DSN with the right privilege levels. If you don't have those levels, then get someone who does and have them create the DSN for you, using an account that has the necessary permissions.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question