jet to sql

I have an access dabase, and need to do the following:

In access I dim a database and a recordset. I then use seek to find if a
record exist.
If it exist I do an edit, if it doesn't exist, I do an addnew. I think any
programmer here gets the idea.

Now, How do I do this in the sql language used with sql server. Above, this
is all done in background, no user interface.
In other words, put in plain english:

Hey database, does TRIP_ID varid exist (seek varid in other words say varid
might be 55147 or whatever here.)
if so
edit
edit record here in code, no user interface.
else
addnew
add record here no user interface. Above isn't the code, but what I'm after
in english. It all works now using DAO.
NOTE: the data is plucked from an open form and stored into a different
table . It needs done like this because another time another table is
used. Trust me, it has to be seperate, not stored in same table open form
is based on. Key billing info is retrieved into a seperate table from more
than one source table. Please don't respond with using one table only.

I saw no seek or find commands in sql.

thanks, a long time programmer who got stumped.[posted and mailed, please reply in news]

JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> Hey database, does TRIP_ID varid exist (seek varid in other words say
> varid might be 55147 or whatever here.)
> if so
> edit
> edit record here in code, no user interface.
> else
> addnew
> add record here no user interface. Above isn't the code, but what I'm
> after in english. It all works now using DAO.

IF EXISTS (SELECT * FROM tbl WHERE keycol = @.keyval)
BEGIN
UPDATE tbl
SET col1 = @.newval1,
col2 = @.newval2,
...
FROM tbl
WHERE keycol = @.keyval
END
ELSE
BEGIN
INSERT tbl (keycol, col1, col2, ...)
VALUES (@.keyval, @.newval1, @.newval2, ...)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Jet to Ansi SQL converter

I've got a weird question --
Have any of you ever come across software that can convert MS Jet SQL to ANSI standard SQL or other SQL flavors?
Could be a huge help in my next project. Please let me know if you know of anything like this!!
:)I'm not aware of any such software. And I probably wouldn't trust any that I came across. I just think it would be a difficult job writing software to interpret and translate all the crappy code that is out there!|||i do know that you can set the global database properties for microsoft access so that it will write it's queries in MS SQL format (ansi 92).

this would allow you to create simple queries in the query designer and then you could modify them and the designer would parse them but you can also do that in the graphical queries in the SQL Server 2000 Enterprise Manager

{For Access Queries in ANSI 92}
open a database
tools > options > Tables/Queries > SQL Server Compatible Syntax (ansi92)
choose this database or default for all new databases.

{For ANSI 92 Queries Graphically in SQL Server}
Open the SQL Server enterprise manager
expand Servers > [your server name] > Databases > [your database name]
open tables or views
right click one of the tables or views and select Open Table > Query
have at it.

i hope this helps

Jet SQL

Hi,

Can I use Jet SQL also for SQL Server 2000 or is it only for Access??

Greetz,

RoelNope, you can use it only for Access. Virtually every database engine extends the SQL standard in proprietary ways, and Access is no different.

Don

jet oledb from tsql

I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
[url]https://msdn.microsoft.com/newsgroups/managed/default.aspx?&query=OPENROWSET&lang=en&cr=US&guid= &sloc=en-us&dg=microsoft.public.sqlserver.programming&p=1&t id=1d7f7e0b-dfea-4606-ac59-dfac726d711e[/url]
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".
[vbcol=seagreen]
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:

> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

jet oledb from tsql

I wish to create a new access database each week whos name includes the
curent date, then to create 7 tables in the database eg '27 Jul 2006.mdb' on
e
for each day
I then wish to insert rows into these tables,
currently I use managed adox & ado to do this, is it possible to use tsql to
get
sql server write the files from its oledb drivers, and would there be any
problems
doing this
in particular I do not know what arguments to pass to opendatasource to
create a new database.Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
https://msdn.microsoft.com/newsgrou...59-dfac726d711e
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".

SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:
[vbcol=seagreen]
> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414 You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/defaul...kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881 How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/defaul...kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Jet OLDB Error on 1/2 of A/A Cluster

Evening Folk ...
Have an Active/Active cluster of SS2K (SP3) on Windows 2003 server.
I have an SP that runs great on one node of the cluster BUT FAILS on the
other node of the cluster.
So in all the DBs (over 50) on NodeA the SP runs and returns the result set.
On NodeB , in all the DBs (over 40), the EXACT SAME SP failes with the
following error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Where should I start to look?
Where do I find where the 'Microsoft.Jet.OLEDB.4.0 is loaded?
Thanks ALL!
Thanks ...
J. KuschOdd that a Microsoft.Jet related error would be coming from a stored
procedure. I guess you have a remote query or are linking to a MS Access,
Excel, etc. file?
Anyhow, yes, I would suspect this failure is the result of MDAC not being
installed correctly on NodeB. However, this error seems to be generic and it
could be the result of an inproperly configured DNS, missing default
database, etc.
http://support.microsoft.com/?id=280102
There is a tool available for download called the MDAC Component Checker
that will determine what version of MDAC is installed and helps diagnose any
installation issues. Also, there is a download for MDAC (currently the
latest version is 2.8). Both are available for download from this location:
http://msdn.microsoft.com/data/mdac...ds/default.aspx
"Jay Kusch" <JayKusch@.discussions.microsoft.com> wrote in message
news:750B0A66-5EF9-4023-BC3E-21ACFD9C497A@.microsoft.com...
> Evening Folk ...
> Have an Active/Active cluster of SS2K (SP3) on Windows 2003 server.
> I have an SP that runs great on one node of the cluster BUT FAILS on the
> other node of the cluster.
> So in all the DBs (over 50) on NodeA the SP runs and returns the result
> set.
> On NodeB , in all the DBs (over 40), the EXACT SAME SP failes with the
> following error:
> Server: Msg 7399, Level 16, State 1, Line 2
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> Where should I start to look?
> Where do I find where the 'Microsoft.Jet.OLEDB.4.0 is loaded?
> Thanks ALL!
>
> --
> Thanks ...
> J. Kusch

Jet ODBC Driver for Text files - Permissions Issue

Hi,
I have setup a linked server to a text file uing the MS Jet ODBC driver.
Users that are not sysadmins and local admins on the server that the file
resides get an Jet Initialize error message.
What are the permissions required for SQL Server users to gain access to
this file?
Thanks.
Arun,
What happens if you run OPENDATASOURCE (See Books Online) against that
provider for the non-admin user account in Query Analyzer? You could also
enable trace flag 7300.
In Query Analyzer you would type something like:
DBCC TRACEON(7300)
go
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',...
Please provide exact error message.
Regards,
James

Jet ODBC Driver for Text files - Permissions Issue

Hi,
I have setup a linked server to a text file uing the MS Jet ODBC driver.
Users that are not sysadmins and local admins on the server that the file
resides get an Jet Initialize error message.
What are the permissions required for SQL Server users to gain access to
this file?
Thanks.Arun,
What happens if you run OPENDATASOURCE (See Books Online) against that
provider for the non-admin user account in Query Analyzer? You could also
enable trace flag 7300.
In Query Analyzer you would type something like:
DBCC TRACEON(7300)
go
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',...
Please provide exact error message.
Regards,
James

JET DB initilization problem help!!!

Hi I have set up a access database on a doc library and a infopath form based on this db published to a nearby folder. When I go to fill out the form, and it opens, I hit submit. But then it gives me a JET DB initilization form with login of admin and a blank pass. When I hit ok, it gives me a error saying

infopath The Microsoft Jet database engine cannot open the file '\\207.245.47.37\sites\Safety_and_Training\Experimental\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

For some reason, once or twice its randomly been able to submit succcessfully but I havnt been able to reproduce the situation. Are there files that i am missing? any permissions that I need to check? The access database only has a admin and no password account. PLEASE HELP ME ive been working on this for ages! If you know a solid workaround with little programming let me know.

Hi,

This is a SQL Server forum, i suggest you direct your question to an Access specific forum where you should find someone able to help.

Cheers,

Leigh

Jet datasource on x64

Running SSRS on a 64bit machine. Have a legacy MS Access database that
we need to use as a datasource for select reports. Appears that Jet
was not ported to x64. Is my only recourse for connecting from SSRS to
Jet to uninstall SSRS x64 and install 32bit version?On Aug 22, 9:30 am, Jim <emailje...@.yahoo.com> wrote:
> Running SSRS on a 64bit machine. Have a legacy MS Access database that
> we need to use as a datasource for select reports. Appears that Jet
> was not ported to x64. Is my only recourse for connecting from SSRS to
> Jet to uninstall SSRS x64 and install 32bit version?
Just a quick followup that I decided against the 32bit version of SSRS
and instead installed an instance of SQL Express which runs 32 bit
inside WOW. SQL Express can use Jet as a linked server. and x64 SQL
Server can link to 32bit express. Not ideal, but gets me the data I
need untill we can do away with MS Access/Jet.
Cheers

JET Database Engine error

I've seen other postings for this error, but I wanted to see if anyone had something a little more solid on how they resolved the issue. When trying to execute a dts package I'm receiving the error:

the number of failing rows exceeds the maximum specified. (Microsoft Jet Database Engine (80004005). Could not update; currently locked. Does anyone have any suggestions? Thanks in advance!yeah, you've probably have data that is corrupt...

Can you modify so it goes in to a table with no contraints?

Where is the file coming from (where is it going)?

It's probably going to be dup primary key, validation rule exception, fk problem, or some other constraint...

just a guess...

JET Anonymous Merge: Can It Be Done?

Is it possible to set up
Publisher: SQL Server 2000
Subscriber: Access 97
Merge replication
Anonymous pull subscriptions
Over the Internet via VPN
I've read the BOL and other on-line artices, but don't quite get it.
Links to specific examples?
Thanks
Nope, you might be able to do a push if you could map a drive. The problem
is the linked server definition to enable access as a database you need to
use if you are replicating to a access database. Review the sample chapter
download in the nwsu link for my book for more info on how to replicate to
access.
The problem is in your term anonymous pull. For a pull to work you must have
SQL Server or MSDE installed on the remote machine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107726821.588526.272280@.l41g2000cwc.googlegr oups.com...
> Is it possible to set up
> Publisher: SQL Server 2000
> Subscriber: Access 97
> Merge replication
> Anonymous pull subscriptions
> Over the Internet via VPN
> I've read the BOL and other on-line artices, but don't quite get it.
> Links to specific examples?
> Thanks
>