Solved
Pull Records From SQL and Access db
Posted on 2014-01-28
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?