Link to home
Start Free TrialLog in
Avatar of dl8453
dl8453

asked on

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.  
Avatar of Julianva
Julianva
Flag of South Africa image

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 ?
Avatar of dl8453
dl8453

ASKER

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.  :)  
Avatar of dl8453

ASKER

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.
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.






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.

Avatar of dl8453

ASKER

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.  :(
Avatar of dl8453

ASKER

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.  :)
Avatar of dl8453

ASKER

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>

ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dl8453

ASKER

agx:

I don't know how to begin using this.  Looks interesting though.  Can you walk me through it?
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#">
Avatar of dl8453

ASKER

#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#">
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?
Avatar of dl8453

ASKER

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.
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




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
Avatar of dl8453

ASKER

_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.
Glad you found a workable solution :) Thanks for adding the note about evaluating the available options. I agree that's very important.

Good Luck!
Avatar of dl8453

ASKER

_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
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>
Avatar of dl8453

ASKER

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.
Okay. If you have any other questions, just let me know.