Solved

Pull Records From SQL and Access db

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now