Help with Insert statemnt selecting from Access database

Hi, I need to select and later Update a database with information I select
from a Access 2000 database. In the following T-SQL, I want to select only
one record for each VName not already exists in my database. Since each
VName have many records in the table, I'm only looking with the record that
has the most recenst date-time, d_DateTime. I keep getting error with this
though. Appreciate it if someone can help me out here.
Thanks, AlphaOops, forgot to past the script.
INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
ORDER BY [d_RemoteName]
"Alpha" wrote:

> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select onl
y
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record tha
t
> has the most recenst date-time, d_DateTime. I keep getting error with thi
s
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Alpha,
Try:
SELECT VName, MAX(d_DateTime)
FROM yourtable
GROUP BY VName
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select
> only
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record
> that
> has the most recenst date-time, d_DateTime. I keep getting error with
> this
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Why not just use DISTINCT and remove the datetime criteria after NOT IN?
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:4ACAE646-A41C-432D-9C65-3B28C3446C6C@.microsoft.com...
> Oops, forgot to past the script.
> INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
> SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
> from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
> ORDER BY [d_RemoteName]
> "Alpha" wrote:
>|||I got error message that it doesn't like the database in "From". Do you kno
w
how to specify a tabe from Access database?
SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
GROUP BY [Data].[d_RemoteName]
Thank you,
Alpha
"Jerry Spivey" wrote:

> Alpha,
> Try:
> SELECT VName, MAX(d_DateTime)
> FROM yourtable
> GROUP BY VName
> HTH
> Jerry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
>
>|||I usually add a linked server definition for the Access database. Take a
look at sp_addlinkedserver in the SQL Server Books Online - there is an
example of how to create one there.
HTH
JErry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>I got error message that it doesn't like the database in "From". Do you
>know
> how to specify a tabe from Access database?
> SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> GROUP BY [Data].[d_RemoteName]
> Thank you,
> Alpha
> "Jerry Spivey" wrote:
>|||Is link server the only option to get the Access data? My application looks
in a directory where each day a new Access file is created with file name
inlcuding the date. I use the most recent file each time the user start my
application and needs to update my database. So you see, the link server
won't work for me because it needs to specify the file location. Unless, I
would delete the link server and create a new one each time my application
starts. But that seems odd and is there even a way to delete the
linkedserver?
Thanks, Alpha.
"Jerry Spivey" wrote:

> I usually add a linked server definition for the Access database. Take a
> look at sp_addlinkedserver in the SQL Server Books Online - there is an
> example of how to create one there.
> HTH
> JErry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>
>|||Great, that works...... Except that I don't think it's getting me the
records that I want. I need to select for oen distinct VNAME record that ha
s
the most recent d_DateTime. I think the script I have below just select a
distinct VName and then plug in the MAx DATE and then the Max Odometer which
each can come from different records with the same VNAME. Anyway to do what
I want.
Thanks a lot, Alpha
"Jerry Spivey" wrote:

> Alpha,
> Try OPENROWSET. From SQL Server BOL:
> C. Use the Microsoft OLE DB Provider for Jet
> This example accesses the orders table in the Microsoft Access Northwind
> database through the Microsoft OLE DB Provider for Jet.
>
> Note This example assumes that Access is installed.
>
> USE pubs
> GO
> SELECT a.*
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
> AS a
> GO
> HTHJerry"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:1876CCCD-5CC0-4185-85FA-A755936B7C62@.microsoft.com...
>
>