Query Existing Data, Query New Data, Append New Data

Scenario:  The scenario is our employees can attend some training at the vendors facility.  The training/test results are sent to us in an access mdb file.  We need to update our SQL database with the test results.  Any employee (userID) can take more than one course so there could be one employee with multiple titleID (course title).  There will not be duplicate userID/titleID pair.  testID is simple the date the test was taken and is paired with titleID.

Need:  We receive an access mdb file from a vendor (A) that contains records (userID, titleID, and testDate).  I need help with a query that will query our current database (B) and then query the new data (A) and update the current database (B) with any new values.  

Field Names:  userID, titleID, and testDate

We are using Coldfusion and SQL2000.  We have the .mdb file setup as a datasource so we can directly query the mdb file.  
LVL 1
dl8453Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JulianvaCommented:
It will be a lot easier if you created a staging database in SQL and then export the access data in the staging database. then wrote sql queries to update the data with new values.

Will all the columns have new values or just one or some of them ?
0
dl8453Author Commented:
Julianva:

Setting up a 'staging database' is a possibility.  

I'm not sure what you mean by "will all the columns have new values..."  We receive weekly updates.  Some of the data that comes to us may already have been sent to us in a previous data feed so we have to query it and remove the entries that have already been sent to us.  Otherwise, I'd just perform a query/insert and be done with it.  The training vendor doesn't want to make it easy on us.  :)  
0
dl8453Author Commented:
One additional note.  One approach I was looking at was to query the mdb file into a comma deliminated list and then writing an SQL script to take the values NOT in the comma deliminated list.  I know how to do this when I'm returning single values but since I need the userID AND the titleID, I'm not sure how to approach this.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

JulianvaCommented:
That wont be a problem  to find the records that already exist. you can write an if exists query to first check if the records exist.
you mentioned update the current database with new values.
to me that is updating a specific value to a new value for that column that you get in your mdb file.
if column 1 had a value of  2 and the mdb file had a value of 6 then you would do an update.
Maybe i misinterpreted the question.

once you create your staging database - export your data from mdb to the database table then write your if exist query to insert records that dont exist in your live database to another table in your staging database then you can easily insert that new data in the live database.using DTS or just sql insert query.






0
JulianvaCommented:
Yes , you can do your matching of new records from access it self. then put those new records in a new table then insert into the sql live database.

0
dl8453Author Commented:
Sorry for the confusion, update is not the correct word, insert would be more appropriate.  I cant use a DTS as the updates will be occurring via buttons on a web page (I've got those worked out using Ajax).  Our server team wont allow us to execute DTS packages from a web server.  :(
0
dl8453Author Commented:
I left out that the datasources for the two data locations are not the same, one datasource source is an access mdb file and the second datasource source is an SQL server.  If I had more points to my name, I would bump my points up.  :)
0
dl8453Author Commented:
I thought I would offer this up in hopes of striking up some ideas...  Here is where I'm starting from... (I hope it pastes ok):

<!--- user_id is numeric but username has letters and numbers (AB1234) --->
<cfquery name="existingData" datasource="#ds_one#" >
      SELECT distinct
            user_id,
            username,
            active
      FROM ytd_user
      ORDER BY
            user_id
</cfquery>
<!---  this is where I'm having trouble --->
<cfset existingData_ids = ''>
<cfoutput query="existingData">
      <cfset existingData_ids = existingData_ids & existingData.user_id>
      <cfif existingData.recordCount is not existingData.currentRow>      
            <cfset existingData_ids = existingData_ids & ','>
      </cfif>
</cfoutput>
<!--- if I can get the above to work, then I think this will fall into place --->
<cfquery name="newData" datasource="#ds_two#" >
      SELECT distinct
            user_id,
            username,
            firstname,
            lastname,
            active
      FROM kpuser
      WHERE
            user_id not in (#existingData_ids#)
</cfquery>

0
_agx_Commented:
dl8453,

Have you investigated whether you can use OPENROWSET, OPENQUERY or OPENDATASOURCE?  They can be used to query an Access database from within SQL Server.  You could pull the Access data into a #temp table and then do your inserts/updates on the live table.  Its not the best solution for every situation but it might be worth looking into.  

Here are a few examples. Caveat - The examples assume the correct drivers, permissions, etc are in place. Check the Books On Line - Transact-SQL Reference for details, restrictions and security recommendations.

--- access database location: c:\TheAccessDatabase.mdb
--- access database table: TestResults

--- 1) use a linked server
USE master
GO
EXEC sp_addlinkedserver
      @server = 'AccessTestResultsDB',
        @provider = 'Microsoft.Jet.OLEDB.4.0',
         @srvproduct = 'OLE DB Provider for Jet',
        @datasrc = 'c:\TheAccessDatabase.mdb'
GO

EXEC sp_addlinkedsrvlogin 'AccessTestResultsDB', 'false', 'sa', 'Admin', NULL
GO

USE YourSQL200DatabaseName
GO

SELECT       UserID, TitleID, TestDate
FROM       OPENQUERY
      (
      AccessTestResultsDB,
      'SELECT UserID, TitleID, TestDate FROM TestResults'
      )

GO


-- 2) use OPENDATASOURCE
SELECT  UserID, TitleID, TestDate
FROM        OPENDATASOURCE
      (
      'Microsoft.Jet.OLEDB.4.0',
       'Data Source="c:\TheAccessDatabase.mdb";
       User ID=Admin;Password='
       )...TestResults

-- 3) use OPENROWSET without an odbc datasource
SELECT       UserID, TitleID, TestDate
FROM       OPENROWSET
      (
      'Microsoft.Jet.OLEDB.4.0',
         'c:\TheAccessDatabase.mdb';'Admin';'',
      TestResults
      )

-- 4) use OPENROWSET with an odbc datasource
SELECT       *
FROM       OPENROWSET
      (
      'MSDASQL', 'TestResultsDSN';'Admin';'',
      'SELECT UserID, TitleID, TestDate FROM TestResults'
      )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dl8453Author Commented:
agx:

I don't know how to begin using this.  Looks interesting though.  Can you walk me through it?
0
_agx_Commented:
Understand they are 4 different examples of how to do the same thing: query an Access database from SQL Server.  If you can read the Access data using your sql server datasource, the rest should be easy.  

Try one of the simpler examples first (either #2 and #3).  Assuming you have the right permissions, they should work from either cfquery or in a Query Analyzer window.  Just change the path to the Access database (c:\YourAccessDatabase.mdb) and the table name (TheAccessTableName). If your Access database is password protected you will also need to change the password and possibly user id.

<cfquery name="TestData" datasource="YourSQLServer2000Datasource" >
   SELECT  UserID, TitleID, TestDate
   FROM    OPENDATASOURCE
          (
          'Microsoft.Jet.OLEDB.4.0',
          'Data Source="c:\YourAccessDatabase.mdb";
          User ID=Admin;Password='
          )...TheAccessTableName
</cfquery>

<cfdump var="#TestData#">
0
dl8453Author Commented:
#2 Gave me this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
and code for #2
<cfquery name="TestData2" datasource="#dbtraining#" >
   SELECT  result_id, user_id, au_sid
   FROM    OPENDATASOURCE
          (
          'Microsoft.Jet.OLEDB.4.0',
          'Data Source="\\servername\folder\Tables.mdb";
          User ID=Admin;Password='
          )...Assess_results
</cfquery>

<cfdump var="#TestData2#">
<hr>
#3 Gave me this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 5: Incorrect syntax near 'Admin'.

here is the code for #3
<cfquery name="TestData3" datasource="#dbtraining#" >
   SELECT  result_id, user_id, au_sid
   FROM    OPENROWSET
          (
          'Microsoft.Jet.OLEDB.4.0',
          'Data Source="\\servername\folder\Tables.mdb",'Admin','',
          )Assess_results
</cfquery>
<cfdump var="#TestData3#">
0
_agx_Commented:
Like I said there are some some restrictions. You can find a much fuller description of the requirements in the sql server Books On Line:
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b

I don't know much about your enviornment. Its possible that you may not have the permissions to use these functions. I would suggest doing a few tests, but first:

1. Do you have administrative (sa) access to the sql server database?
2. Do you have access to a tool like Enterprise Manager?
3. Do you have permission to make registry changes on the server hosting the sql server database?
4. Do you have permission to create ODBC datasources on the server hosting the sql server database?
5. Is it possible to copy the Access database to the server hosting the sql server database so you can use a local path like c:\theDatabase.mdb instead of a unc path?
0
dl8453Author Commented:
1. Do you have administrative (sa) access to the sql server database?
Yes.
2. Do you have access to a tool like Enterprise Manager?
Yes
3. Do you have permission to make registry changes on the server hosting the sql server database?
No
4. Do you have permission to create ODBC datasources on the server hosting the sql server database?
I can have an ODBC setup.
5. Is it possible to copy the Access database to the server hosting the sql server database so you can use a local path like c:\theDatabase.mdb instead of a unc path?
The file is already on the sql server.
0
_agx_Commented:
Regarding Answer #3 and #4

"BOL:   OPENDATASOURCE/OPENROWSET  can be used to access remote data from OLE DB data sources only if the DisallowAdhocAccess registry option is explicitly set to 0. When this option is not set, the default behavior does not allow ad hoc access."

You have to change a registry setting in order to use those functions. If the setting can't be changed then I don't think you can use OPENDATASOURCE/OPENROWSET.

Regarding Answer #5
Try creating a linked server.  Since both databases are on the same server, try using a local path instead of unc.

You can create a linked server using TSQL or using Enterprise Manager.  Using TSQL there are two steps 1) create the linked server and 2) set the permissions. You only have to do this once. After you create the linked server, you can call it your queries almost like a regular sql server table.


--- 1) create the linked server
USE master
GO
EXEC sp_addlinkedserver
        @server = 'AccessTestResultsDB',
        @provider = 'Microsoft.Jet.OLEDB.4.0',
         @srvproduct = 'OLE DB Provider for Jet',
        @datasrc = 'c:\TheAccessDatabase.mdb'
GO

-- 2) set the permissions
USE master
GO

EXEC sp_addlinkedsrvlogin 'AccessTestResultsDB', 'false', 'sa', 'Admin', NULL
GO

If it works you should be able to query the linked server like this:
 
SELECT       UserID, TitleID, TestDate
FROM       OPENQUERY
      (
      AccessTestResultsDB,
      'SELECT UserID, TitleID, TestDate FROM TestResults'
      )

GO




0
_agx_Commented:
Using Enterprise Manager:  Security -> Linked Servers -> New Linked Server

[General Tab]
Linked server: (Enter any name like "AccessTestResultsDB")
Other Datasource: "Microsoft Jet 4.0 OLE DB Provider"
Product Name: (Empty)
Data source:   c:\TheAccessDatabase.mdb
Provider String: (Empty)

You may also need to set Security properties. Example

[Security Tab]
Local Login: sa
Impersonate: (Not checked)
Remote user: Admin
Remote Password: (Empty)

Click OK


You might also find this kb article helpful

HOW TO: Use the DisallowAdHocAccess Setting to Control Access to Linked Servers
http://support.microsoft.com/kb/327489
0
dl8453Author Commented:
_agx_

I'm meeting with the Server Team to get the Linked Table setup.  I spoke with them briefly and this will be the easiest way to do this and the easiest in terms of maintenance, too.  Then I'll be able to query both datasources and my problems will be solved.  :)

If anyone else comes across this page, as _agx_ stated, there are numerous solutions but I just accepted the one that I implemented.  My suggestion to you would be evaluate each one and determine which one is best for your application.  My server team is very easy to work with and accomodating.
0
_agx_Commented:
Glad you found a workable solution :) Thanks for adding the note about evaluating the available options. I agree that's very important.

Good Luck!
0
dl8453Author Commented:
_agx_ If you are still watching this, my SQL guys got the access db file setup as a linked server.  I can query the access db file via query analyzer just fine.  I don't know how to write the cfquery statement though.  Do I need to include a datasource now or what?

<cfquery name="openQuery" datasource="#dev#">
select * from OPENQUERY(UserData, 'SELECT id, uname, score FROM tRecords')
</cfquery>

datasource="#dev#"   == datasource name as setup in CF Server (Administrator)
UserData == DSN Name
tRecords == table withing the access.mdb file
0
_agx_Commented:
Just take the sql you ran in the query analyzer and add your sql server datasource name.  

sqlserverdsn == name of datasource you normally use to query your sql server tables
UserData == name of the linked server created for the access database
tRecords == table withing the access.mdb file

<cfquery name="openQuery" datasource="#sqlserverdsn#">
select * from OPENQUERY(UserData, 'SELECT id, uname, score FROM tRecords')
</cfquery>
0
dl8453Author Commented:
Okay, thanks.  My query is right but still getting an erorr.  

[Microsoft][ODBC SQL Server Driver][SQL Server]Access to the remote server is denied because no login-mapping exists.

My server guy knows how to fix this error.  Thanks for the correction to my query.
0
_agx_Commented:
Okay. If you have any other questions, just let me know.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.