Append Query not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Append Query not working

Author

Topic

evansra
Starting Member

3 Posts

Posted - 2005-10-19 : 08:27:45

Hi everyone,

I have an SQL database that i want to run an append query on so that it compares the data in the SQL database with an Access database i have and appends data into the access database accordingly.

I have this SQl statement but it does not seem to work when i execute it on DTS. (plantmetricsdata is my linked server name where i have linked to the access database)

INSERT INTO plantmetricsdata...BatchCount (Building, CountTime, CountGroup, BatchCount)
SELECT "901" AS Building, CountTime, CountGroup, BatchCount
FROM [Matrics Data].dbo.BatchCount
WHERE CountTime>dmax("CountTime","plantmetricsdata...BatchCount", "Building ='901")

When i try to execute this step, i get an error saying:

Error Source: Microsoft Jet Engine

Error Description: Could not find file C:\program files\Microsoft SQL server\80\tools\binn\Matrics Data.mdb

Which is odd because i have never refered to that file or saved anything in that directory.

Many Thanks

Any ideas why?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-19 : 08:46:40

try replacing:

dmax("CountTime","plantmetricsdata...BatchCount", "Building ='901") <-- note there is an error here anyway, a missing ending ' in the condition

with the more standard:

(Select Max(CountTime) from PlantMetricsData...BatchCount WHERE Building ='901')

I don't think that the JET database engine supports DMIN, DMAX, etc -- I believe those are VBA functions that are part of the MS Access object model and thus require MS Access to be open to evaluate.

evansra
Starting Member

3 Posts

Posted - 2005-10-19 : 08:58:17

I see, so how can i find the maximum of a column without using DMAX?

evansra
Starting Member

3 Posts

Posted - 2005-10-19 : 09:08:49

Thanks for your help. I ammended the SQL statement to read:

INSERT INTO plantmetricsdata...BatchCount (Building, CountTime, CountGroup, BatchCount)
SELECT "901" AS Building, CountTime, CountGroup, BatchCount
FROM [Matrics Data].dbo.BatchCount
WHERE CountTime>(Select Max(CountTime) from PlantMetricsData...BatchCount WHERE Building ='901')

And now i get the error:

"ADO Error: Invalid column name '901'. Statements could not be prepared. Defered prepare could not be completed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources