JS List
asked on
Pull Records From SQL and Access db
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].[doc Permission s] AS docPermissions "
strSQL = strSQL + "ON HR_Employees.EmployeeNumbe r = 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(B yVal strSQL As String) As IEnumerable
Dim cmd As SqlCommand
cmd = New SqlCommand(strSQL, New SqlClient.SqlConnection(Ge tConnectio nString))
cmd.Connection.Open()
Return cmd.ExecuteReader(CommandB ehavior.Cl oseConnect ion)
cmd.Connection.Close()
End Function
The error comes up as:
Invalid object name 'MS Access;DATABASE=C:\inetpub \wwwroot\I 4\App_Data \documentL ibrary.mdb .docPermis sions'.
Any ideas?
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].[
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
strSQL = strSQL + "ON HR_Employees.EmployeeNumbe
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(B
Dim cmd As SqlCommand
cmd = New SqlCommand(strSQL, New SqlClient.SqlConnection(Ge
cmd.Connection.Open()
Return cmd.ExecuteReader(CommandB
cmd.Connection.Close()
End Function
The error comes up as:
Invalid object name 'MS Access;DATABASE=C:\inetpub
Any ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.