Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pull Records From SQL and Access db

Posted on 2014-01-28
3
Medium Priority
?
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 85

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

598 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