Solved

Pull Records From SQL and Access db

Posted on 2014-01-28
3
259 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 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 1

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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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