?
Solved

Pull Records From SQL and Access db

Posted on 2014-01-28
3
Medium Priority
?
267 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 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 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

771 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