Objects related to DB: access

Showing posts with label access. Show all posts

Friday, March 30, 2012

ODBC Error

I get the following error (from my front-end Access app AND Query Analyzer)
when accessing a particular table. It is a rather large table, ~1,750,000
rows in it. I tried looking through "limitations" in BOL and couldn't find
anything. Is this a server setting or ODBC setting or... ?
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionChe
ckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokenWhat are you trying to do with that much data? Access isn't designed
to handle scrolling through millions of rows, although you may be
having network issues, as the error message suggests. Restrict the
data fetched with a WHERE clause, or if it's a report, write a stored
procedure and call it through a pass-through query.
--mary
On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>I get the following error (from my front-end Access app AND Query Analyzer)
>when accessing a particular table. It is a rather large table, ~1,750,000
>rows in it. I tried looking through "limitations" in BOL and couldn't find
>anything. Is this a server setting or ODBC setting or... ?
>[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCh
eckForData
>(CheckforData()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation.
>Connection Broken
>|||What I'm trying to do is populate a local table (BackOrders) that's actually
used in the form from a server table (SalesDetail). There is a constraint
(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
the entire table. Here is the original SQL (pure Access 97 app):
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder > 0
ORDER BY SalesDetail.invoiceDate DESC
Even if this worked as-is with SQL Server (it doesn't - gives the same
error), it would be horribly slow, so I wanted to optimize it by creating a
View of the server tables. But I don't know the value of 'x' (Customer)
until run time. So I used the following DDL to create the View on SQL
Server:
USE Prototype
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vBackOrder')
DROP VIEW vBackOrder
GO
CREATE VIEW vBackOrder AS
SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty -
Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
GO
I linked vBackOrder in Access then used this DAO code to populate local
table BackOrders:
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
vBackOrder WHERE vBackOrder.Customer=x
This also gives the same error, as does attempting to open the linked
vBackOrder in Access. I then went to QA and tried just the SELECT portion of
the CREATE VIEW with the same result. To narrow it down, I tried SELECTing *
FROM Inventory - a little slow but no problem. I then tried the same thing
with SalesDetail and again get the same error. I have >300 server tables of
varying sizes in this app. SalesDetail is by far the largest, and it is the
*only* one I get the error on. So I'm assuming it has something to do with
the size.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
> What are you trying to do with that much data? Access isn't designed
> to handle scrolling through millions of rows, although you may be
> having network issues, as the error message suggests. Restrict the
> data fetched with a WHERE clause, or if it's a report, write a stored
> procedure and call it through a pass-through query.
> --mary
> On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
Analyzer)[vbcol=seagreen]
find[vbcol=seagreen]
>|||DAO is the problem. You're loading the Jet engine and using it for SQL
Server data operations, something it was never designed or optimized
to do. Create a stored procedure instead of a buinch of views. Stored
procedures support parameters and complex logic, and return a
read-only result set which you can use to populate your local table.
Call the stored procedure from a pass-through query where you set the
SQL syntax to something like this in your code (you can use DAO to set
properties of a QueryDef object and execute it):
qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
qdef.Execute
Pass-through queries bypass the Jet engine when they're executed and
are the most efficient way of getting back large result sets since all
of the processing takes place on the server, not in Jet. You then
create either an Insert or Update query that selects from your
pass-through query into the local table. When you call the
insert/update query it will automatically execute the pass-through
query to get the records.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>What I'm trying to do is populate a local table (BackOrders) that's actuall
y
>used in the form from a server table (SalesDetail). There is a constraint
>(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
>the entire table. Here is the original SQL (pure Access 97 app):
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
>SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
>SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
>SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
>SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
>AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder >
0
>ORDER BY SalesDetail.invoiceDate DESC
>Even if this worked as-is with SQL Server (it doesn't - gives the same
>error), it would be horribly slow, so I wanted to optimize it by creating a
>View of the server tables. But I don't know the value of 'x' (Customer)
>until run time. So I used the following DDL to create the View on SQL
>Server:
>USE Prototype
>GO
>IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'vBackOrder')
> DROP VIEW vBackOrder
>GO
>CREATE VIEW vBackOrder AS
>SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
>SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty
-
>Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
>SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
>SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
>SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
>SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
>GO
>I linked vBackOrder in Access then used this DAO code to populate local
>table BackOrders:
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
>vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
>vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
>vBackOrder WHERE vBackOrder.Customer=x
>This also gives the same error, as does attempting to open the linked
>vBackOrder in Access. I then went to QA and tried just the SELECT portion o
f
>the CREATE VIEW with the same result. To narrow it down, I tried SELECTing
*
>FROM Inventory - a little slow but no problem. I then tried the same thing
>with SalesDetail and again get the same error. I have >300 server tables of
>varying sizes in this app. SalesDetail is by far the largest, and it is the
>*only* one I get the error on. So I'm assuming it has something to do with
>the size.
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
>Analyzer)
>find
>|||Hi Mary,
BTW I have your book and it is very good. Unfortunately, for this project I
am stuck with Access 97 and the book seems to reference Access 2000 +.
Thanks for your help and that not only works but it is *much* faster! Looks
like I'll be retrofitting all of those Views I created! Thanks again!
Regards,
Ron Hinds
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
> DAO is the problem. You're loading the Jet engine and using it for SQL
> Server data operations, something it was never designed or optimized
> to do. Create a stored procedure instead of a buinch of views. Stored
> procedures support parameters and complex logic, and return a
> read-only result set which you can use to populate your local table.
> Call the stored procedure from a pass-through query where you set the
> SQL syntax to something like this in your code (you can use DAO to set
> properties of a QueryDef object and execute it):
> qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
> qdef.Execute
> Pass-through queries bypass the Jet engine when they're executed and
> are the most efficient way of getting back large result sets since all
> of the processing takes place on the server, not in Jet. You then
> create either an Insert or Update query that selects from your
> pass-through query into the local table. When you call the
> insert/update query it will automatically execute the pass-through
> query to get the records.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
actually[vbcol=seagreen]
fetch[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
WHERE[vbcol=seagreen]
SalesDetail.QtyShipped[vbcol=seagreen]
> 0
a[vbcol=seagreen]
SalesDetail.invoiceDate,[vbcol=seagreen]
Inventory.Qty -[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
of[vbcol=seagreen]
SELECTing *[vbcol=seagreen]
thing[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
with[vbcol=seagreen]
~1,750,000[vbcol=seagreen]
Sockets]ConnectionCheckForData[vbcol=sea
green]
>|||Although the book is for a newer version, the basic concepts remain
the same, and always will, which are: fetch only needed data and
perform as much data processing on the back end. Let the FE do
presentation tasks like formatting, etc. All of the code in the
chapters for linked tables will work pretty much as-is in Access 97.
--Mary
On Mon, 19 Apr 2004 14:08:04 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>Hi Mary,
>BTW I have your book and it is very good. Unfortunately, for this project I
>am stuck with Access 97 and the book seems to reference Access 2000 +.
>Thanks for your help and that not only works but it is *much* faster! Looks
>like I'll be retrofitting all of those Views I created! Thanks again!
>Regards,
>Ron Hinds
>
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
>actually
>fetch
>QtyOrdered,
>FROM
>WHERE
>SalesDetail.QtyShipped
>a
>SalesDetail.invoiceDate,
>Inventory.Qty -
>QtyOrdered,
>FROM
>of
>SELECTing *
>thing
>of
>the
>with
>~1,750,000
>Sockets]ConnectionCheckForData
>|||Ron,
Switch to an Access 2002 data project and dump the mdb. You are looking at
a complete rewrite. Access 2002 ADPs are VERY EASY to use. Access linked t
ables create multiple connections and are prone to creating deadlocks and mu
ltitudes of other problems.
Linked tables in Access 97 worked 'OK', linked tables in 2000 and on are not
usable in a production system.
"Ron Hinds" wrote:

> I get the following error (from my front-end Access app AND Query Analyzer
)
> when accessing a particular table. It is a rather large table, ~1,750,000
> rows in it. I tried looking through "limitations" in BOL and couldn't find
> anything. Is this a server setting or ODBC setting or... ?
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionC
heckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>

ODBC Driver to Connect With IBM AS/400 System

Good Morning All:
I am trying to find an ODBC Driver that will connect my current
Microsoft Access Databas with our IBM AS/400 System. We have the
driver in Excel, but I don't believe it is the same driver for
Access. Any input would be greatly appreciated.
-Anthony Morano
Pension Fund Intern.
The ODBC driver for DB2 on AS/400 as provided by IBM; it is the same driver
for all client applications. Contact you IBM SE to purchase the client
connectivity software if you don't already have it. Otherwise try an SQL/400
newsgroup.
<antmorano@.gmail.com> wrote in message
news:1184852139.438805.176820@.e9g2000prf.googlegro ups.com...
> Good Morning All:
> I am trying to find an ODBC Driver that will connect my current
> Microsoft Access Databas with our IBM AS/400 System. We have the
> driver in Excel, but I don't believe it is the same driver for
> Access. Any input would be greatly appreciated.
> -Anthony Morano
> Pension Fund Intern.
>
sql

ODBC Driver to Connect With IBM AS/400 System

Good Morning All:
I am trying to find an ODBC Driver that will connect my current
Microsoft Access Databas with our IBM AS/400 System. We have the
driver in Excel, but I don't believe it is the same driver for
Access. Any input would be greatly appreciated.
-Anthony Morano
Pension Fund Intern.The ODBC driver for DB2 on AS/400 as provided by IBM; it is the same driver
for all client applications. Contact you IBM SE to purchase the client
connectivity software if you don't already have it. Otherwise try an SQL/400
newsgroup.
<antmorano@.gmail.com> wrote in message
news:1184852139.438805.176820@.e9g2000prf.googlegroups.com...
> Good Morning All:
> I am trying to find an ODBC Driver that will connect my current
> Microsoft Access Databas with our IBM AS/400 System. We have the
> driver in Excel, but I don't believe it is the same driver for
> Access. Any input would be greatly appreciated.
> -Anthony Morano
> Pension Fund Intern.
>

ODBC driver problem occurs on table create

Hi all!

I get the following error when trying to create a table:
[Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or access denied.[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Connect()).

I use the same connection for table updates and selects and never have a problem. This only happens on table create. It also only happens on one machine (Win2K with SQL server 2000 Enterprise Edition). I've run this app without getting this problem on at least ten other machines (mostly WinXP, but also a few Win2K).

Any ideas?

nscdDoes the account that you're using have permissions on that SQL Server to create new tables?|||Yes it does.

I've also changed it so that I connect using the sa account, but still get the same problem.

Cheers

nscd|||Hi all

Further investigation has revealed that my client's machine was fairly out of date and installing the latest MDAC patch from MS resolved the problem.

Cheers

nscd

Wednesday, March 28, 2012

ODBC Driver for SQLServer Everywhere/Compact from Access

So far I've been unable to connect to a test SQLServer Everywhere/Compact database with MS Access. I installed the new SQLServer 2005 Native Client to no avail. Has anyone done this?

If you are trying to sync the data between Access and the SQLServer Compact edition, you might need to download the synchronizer. The following link should provide you a quick overview and the access to the software. If this is not what you intended to do, please provide more details on what you are trying to accomplish:

http://www.microsoft.com/downloads/details.aspx?FamilyID=B967347A-5DD0-445C-8A9F-AEA3DB9EC4BC&displaylang=en

Regards,

Riyaz

|||

In Access, I'm simply trying to link to a SQLServer CE database using an ODBC driver, as you can do with other SQLServer servers. I've been the route you suggested, hoping that in the process I'd discover the driver I need. But to install that synchronizer, I have to install IIS and SQLServer 2005, which goes beyond what I'm trying to do. And I'm not interested in syncronizing to a mobile device in any case.

I was hoping to use SQLServer CE as a substitute for the normal Access Jet engine, to take advantage of encryption and security that Access does not provide (or does poorly). I want to deploy a single-user Access application with an easily deployable secure database. I'm doing it now with just Access, but I'd like to make the data tables inaccessible except through my application.

Thanks for the suggestion.

|||You can only connect to an instance of a SQL Server 2005 Mobile Edition database when it is running on a Windows CE-based OS or Tablet PC (you can create a SQL Server 2005 Mobile database in Visual Studio and, I believe, SQL Server Management Studio as part of the development process). When SQL Server Everywhere becomes available, it should, as you've surmised, fit your needs. In the meantime, you can check out the development experience using SQL Mobile today...take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/sqleverywhere.asp?frame=true for some additional info. If you'd like to have a runtime prototype of your app, however, you'll want to try using SQL Server 2005 Express.|||

I do have SQL Server 2005 Everywhere installed, (SQL Server 2005 Compact Edition), and I've created a database from VS 2005 (after installing VS 2005 SP1 Beta) and so far have not been able to connect to that database from Access. There is a .dll distributed with Everywhere: sqlceoledb30.dll that has a description "OLEDB Provider" that I am hoping will work, but it is not installed as an ODBC driver.

It seems impossible that I'm the first to attempt this. Any help will be appreciated.

Office 2003 SP2, Win XP SP2, VS 2005 SP1 Beta

Thanks

|||

The naming is a little tricky here...SQL Server 2005 Mobile Edition is the 3.0 version of the product formerly referred to as SQL Server Compact Edition (or SQL CE). The product formerly referred to as SQL Server Everywhere now appears to have the moniker of SQL Server 2005 Compact Edition...this version is the only one in this family that can be used on the desktop. A CTP version of this product was released in late August and the RC1 version is now available at http://www.microsoft.com/downloads/details.aspx?FamilyId=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en

Given all of this, which version do you have? The RC1 file version appears to be 3.1 (the device only SQL Server 2005 Mobile Edition would be v3.0, I believe).

If you have the correct version but continue to have problems connecting, I'll move this thread into the SQL Server Compact Edition forum.

|||

If you read the last post I made, it's pretty clear that I have Compact Edition 3.1 RC1 Beta. I haven't ever installed the Mobile Edition. After installing CE, most references (like the installation directory) reflect the previous name (Everywhere).

I really want to link to a Compact Edition database through ODBC (as it happens, in Access). I can connect to it fine in VS 2005 VB.NET, so I don't think it's a lack of understanding.

Thanks, any help will be appreciated.

|||Thanks for claryifying the specific version that you have installed. Someone here in the SQL Server Compact Edition forum should be able to help you with the nuances of connecting to a local SQL Server CE database.|||

Although SQL CE 3.1 does expose an OLE DB provider, you cannot link to it via the regular ODBC link in Access. I have developed a set of tools that will help you with both converting to and from Access and also to edit the SDF on the desktop. These tools use the OLE DB provider at the low level and work with databases on the PC and on the device. You can try them here: http://www.primeworks-mobile.com

ODBC Driver for AS400

Hi,

I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.

Thanks,

VivekLook at www.hitsw.com. I've never used their drivers, but a friend of mine did and he praised their performance to the sky.

Regards,

hmscott

Hi,

I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.

Thanks,

Viveksql

ODBC Driver for Access and AS/400

Good Morning All:
I am trying to find an ODBC driver for Microsoft Access that will
allow me to connect to our IBM AS/400 system. All inpout would be
appreciated.
-Anthony Morano
Pension Fund InternYou can use the Client Access driver you get with the client
for the AS/400.
You may want to post future questions to one of the Access
newsgroups. This group addresses ODBC and SQL Server.
-Sue
On Thu, 19 Jul 2007 14:06:15 -0000, antmorano@.gmail.com
wrote:

>Good Morning All:
>I am trying to find an ODBC driver for Microsoft Access that will
>allow me to connect to our IBM AS/400 system. All inpout would be
>appreciated.
>-Anthony Morano
>Pension Fund Intern

ODBC Driver for Access and AS/400

Good Morning All:
I am trying to find an ODBC driver for Microsoft Access that will
allow me to connect to our IBM AS/400 system. All inpout would be
appreciated.
-Anthony Morano
Pension Fund Intern
You can use the Client Access driver you get with the client
for the AS/400.
You may want to post future questions to one of the Access
newsgroups. This group addresses ODBC and SQL Server.
-Sue
On Thu, 19 Jul 2007 14:06:15 -0000, antmorano@.gmail.com
wrote:

>Good Morning All:
>I am trying to find an ODBC driver for Microsoft Access that will
>allow me to connect to our IBM AS/400 system. All inpout would be
>appreciated.
>-Anthony Morano
>Pension Fund Intern

Monday, March 26, 2012

ODBC database slows down over time

Howdy,

I currently have an ODBC connection to a SQL server that exists on the other side of the country from me. I'm running queries in Access and I've found that over time, queries run slower and slower. If I create a new database and bring in the same tables, it runs quickly (relatively speaking) again. Why is this happening? Is there something I can do besides creating new databases all the time?

Thank you in advance.Is the problem that over time the query has more and more records to move through? Is the DB normalized?

Friday, March 23, 2012

ODBC Connection using Alternate windows credentials

Hi,
We have a server in a workgroup that users in our active directory
will need to access.
How can we configure an odbc connection to use a different username
and password (windows not sql) than the one they are currently logged
on on as. (The application we are using only supports windows
authentication not sql)
example : I am logged onto my workstaion a 'Jane', but i Need to use
user id 'janey' to connect to the odbc, and i dont want to have to
log
on to the workstation as janey
Hi
Unless you are logged on as the user you wish to authenticate as I don't
think you can do this!
John
"zach" <neopotent@.gmail.com> wrote in message
news:5e0669bb-e86f-4610-8713-5bd06084daa1@.a23g2000hsc.googlegroups.com...
> Hi,
> We have a server in a workgroup that users in our active directory
> will need to access.
>
> How can we configure an odbc connection to use a different username
> and password (windows not sql) than the one they are currently logged
> on on as. (The application we are using only supports windows
> authentication not sql)
>
> example : I am logged onto my workstaion a 'Jane', but i Need to use
> user id 'janey' to connect to the odbc, and i dont want to have to
> log
> on to the workstation as janey
>

ODBC Connection using Alternate windows credentials

Hi,
We have a server in a workgroup that users in our active directory
will need to access.
How can we configure an odbc connection to use a different username
and password (windows not sql) than the one they are currently logged
on on as. (The application we are using only supports windows
authentication not sql)
example : I am logged onto my workstaion a 'Jane', but i Need to use
user id 'janey' to connect to the odbc, and i dont want to have to
log
on to the workstation as janeyHi
Unless you are logged on as the user you wish to authenticate as I don't
think you can do this!
John
"zach" <neopotent@.gmail.com> wrote in message
news:5e0669bb-e86f-4610-8713-5bd06084daa1@.a23g2000hsc.googlegroups.com...
> Hi,
> We have a server in a workgroup that users in our active directory
> will need to access.
>
> How can we configure an odbc connection to use a different username
> and password (windows not sql) than the one they are currently logged
> on on as. (The application we are using only supports windows
> authentication not sql)
>
> example : I am logged onto my workstaion a 'Jane', but i Need to use
> user id 'janey' to connect to the odbc, and i dont want to have to
> log
> on to the workstation as janey
>

ODBC connection to SQL server failing

Hi, I have an access 2003 database which connect to sql server via a
DSN that goes through a VPN. The database works fine and everyone can
coonect from their normal computers but for anyone working on their
Laptops (IBM Think Pad T42's) they cannnot connect to the datasource.
The DSN fails upon connection attempt with :

Connection Failed:
SQLState: '01000'
SQL Server Error: 1326
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access is denied.

This has been driving me insane as i cannot understand why it works for
our desktop computers and not for our laptops. The version of MDAC is
the same on a computer that connects as it is on a laptop that doesn't.
TCP/IP is enabled on the server and is using the default 1433 port.
severs, desktops and laptops all patches and up to date with the latest
SP's. DSN has been dropped and re-created. Domain user has been put in
localAdmin group for laptop.

I hope i'm mising something really obvious...can someone please please
put me out of my misery and tell me they have come accross this before
and have a solution. Ive tried everything i can think of.

Cheers

DanDan (dan_barber2003@.hotmail.com) writes:
> Hi, I have an access 2003 database which connect to sql server via a
> DSN that goes through a VPN. The database works fine and everyone can
> coonect from their normal computers but for anyone working on their
> Laptops (IBM Think Pad T42's) they cannnot connect to the datasource.
> The DSN fails upon connection attempt with :
> Connection Failed:
> SQLState: '01000'
> SQL Server Error: 1326
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Connection Failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> or access is denied.

The problem is that the laptop somehow does not find the SQL Server.
This KB article discusses posible reasons:
http://support.microsoft.com/defaul...B;EN-US;q328306

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland. I had found that article and gone through (as best i
could) all the options but still had no joy. I have finally solved it
though. On our desktops we have microsoft firewall client installed and
enabled but on the laptops we dont. For some reason it seems that we
cannot connect through our proxy server and a connection to sql server
has to be done through this firewall client?? Even though i have solved
the problem i am still not sure why this happened??

Thanks again

Dan|||Dan (dan_barber2003@.hotmail.com) writes:
> Thanks, Erland. I had found that article and gone through (as best i
> could) all the options but still had no joy. I have finally solved it
> though. On our desktops we have microsoft firewall client installed and
> enabled but on the laptops we dont. For some reason it seems that we
> cannot connect through our proxy server and a connection to sql server
> has to be done through this firewall client?? Even though i have solved
> the problem i am still not sure why this happened??

It sounds a little funny, I will have to admit. But being nowhere
close to a Windows networking expert, I don't have any explanations.

But if you have the SQL connection working, and also have the firewall
enabled on the laptops, that sounds like a double win to me.

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

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

odbc connection to access

Hello,

I am a newcomer to databases in general and I am having some difficulties. I have an access database that i want to link to sql. I have trauled the web and notice that ODBC connection seems to be the recommended way.
I have set up a DSN which points to the access database but am now stuck as to what to do in sql to be able to query the database.
Could someone please help?
Many thanksHere is how you can connect. HTH

Set cnndb= New ADODB.Connection
cnndb.ConnectionString = "DSN=<dsnname>;UID=<userID>;PWD=<pwd>;"
cnndb.Open

setup record set .
set rs=new adodb.recordset
rs.open (<your sql statement>)

set rs=nothing
set cnndb=nothing|||Thank you sivaroo,
How do I query the connection?
I know it sounds thick but I really am i newboy|||To see if connection is successful you can use

if cnndb.state = 0 not connected
if cnndb.state = 1 connected

lets say you have sql like this.

strSql="Select * from table1"

once you created connection like previously mentioned.

dim rs as adodb.recordset
dim data1 as string

Set rs = Cnndb.Execute(strSql) 'this will pull data. then you can loop throug it to see
do while not rs.eof
data1=rs.fields("Column1").value
rs.movenext
loop

remeber to close like previously mentioned.|||Hello,

if you don't want to use VBA you can connect to your sql-server in different way.

Make up a new dsn for your sql server.
-> goto your tables object browser
-> press new
-> link table
-> filetyp : odbc database
-> tabstrip computer data source
-> choose your dsn
-> select your table

the table will now appear as a 'normal' table in your object browser.
You can query this like a regular table.
(Note that i use a german access so i only translated the menu items back to english)

Wednesday, March 21, 2012

ODBC Connection in UNIX

Hi Folks,
I need to access the MSSQL data in Oracle.
For that i need to first create the data source (ODBC Connection) to the MSSQL server on UNIX. Can anybody help me in creating the ODBC connection.
Thanks,
AmitI think you find what you're looking for at DataDirect (http://www.datadirect.com/products/odbc/index.ssp).

ODBC connection failure

ODBC Connection failure

Hello there!

This is my issue: I do have a database which back end is in SQL Server 2005 and front end is in ACCESS 2003. When one of the forms is trying to write duplicate information in any of the tables, there is a message coming "ODBC -- call failure", I wrote code trying to avoid that situation, but is keeps on coming. Does anybody have any idea about how to handle this exception? I would really appreciate a help on this subject.

Thanks;

Luisofo

One approach is to add an On Error handler to the Access Form. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/achowRuntimeErrorHandling_HV05186924.asp In the On Error handler you can use the Err object to determine the source of the error and provide useful information to the user to correct the problem.

If you are ok with duplicates in the table (not usually the situation) you can remove the constraints on the SQLServer 2005 datgbase tables.

HTH.

sql

ODBC connection failure

After upgrading to Windows 2003 Server, I can no longer
access SQL Server via ODBC. It says the SQL Server
doesn't exist or access denied. None of the settings have
changed after the upgrade.
Is there a fix to this?
Thanks in advance.(a) stop using ODBC, it's been deprecated. OLEDB is much preferred.
(b) SQL Server authentication? Windows authentication? What is the
username you are using? What kind of account is it? Have you tried a
different authentication method and/or a different user?
(c) http://support.microsoft.com/?kbid=328306
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:19b9c01c44d6d$b0030b20$a401280a@.phx.gbl...
> After upgrading to Windows 2003 Server, I can no longer
> access SQL Server via ODBC. It says the SQL Server
> doesn't exist or access denied. None of the settings have
> changed after the upgrade.
> Is there a fix to this?
> Thanks in advance.|||How would I switch to the OLEDB method? It's SQL Server
auth., and also tried different methods and users.
Also, I found that the ODBC Driver versions are different
on the client machines. Would I need to update the
clients to the new version?
>--Original Message--
>(a) stop using ODBC, it's been deprecated. OLEDB is much
preferred.
>(b) SQL Server authentication? Windows authentication?
What is the
>username you are using? What kind of account is it?
Have you tried a
>different authentication method and/or a different user?
>(c) http://support.microsoft.com/?kbid=328306
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message
>news:19b9c01c44d6d$b0030b20$a401280a@.phx.gbl...
>> After upgrading to Windows 2003 Server, I can no longer
>> access SQL Server via ODBC. It says the SQL Server
>> doesn't exist or access denied. None of the settings
have
>> changed after the upgrade.
>> Is there a fix to this?
>> Thanks in advance.
>
>.
>|||> How would I switch to the OLEDB method?
That really depends... what application/language are you using for the
client applications?
> Also, I found that the ODBC Driver versions are different
> on the client machines. Would I need to update the
> clients to the new version?
Yes, apply the latest MDAC all around. You should try to avoid mismatched
versions between client and server.
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Installing the MDAC results in the same SQL Server does
not exist error.
All of the clients are using Windows XP Pro to connect to
the SQL Server.
>--Original Message--
>> How would I switch to the OLEDB method?
>That really depends... what application/language are you
using for the
>client applications?
>> Also, I found that the ODBC Driver versions are
different
>> on the client machines. Would I need to update the
>> clients to the new version?
>Yes, apply the latest MDAC all around. You should try to
avoid mismatched
>versions between client and server.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-b037-
185d0506396c&DisplayLang=en
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)|||> All of the clients are using Windows XP Pro to connect to
> the SQL Server.
That's the operating system, but it doesn't tell me anything about the
application that is attempting to make a connection. Are you trying to
connect from Excel, notepad, ColdFusion, Query Analyzer, the ODBC control
panel applet, ... ?
Have you gone through http://support.microsoft.com/?kbid=328306 ?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I'm trying to connect from the ODBC Control Panel.
Yes, I have through the 328306 article and haven't found
any resolutions.
Would the easiest thing to do be revert back to Windows
2000 when this all worked correctly?
>--Original Message--
>> All of the clients are using Windows XP Pro to connect
to
>> the SQL Server.
>That's the operating system, but it doesn't tell me
anything about the
>application that is attempting to make a connection. Are
you trying to
>connect from Excel, notepad, ColdFusion, Query Analyzer,
the ODBC control
>panel applet, ... ?
>Have you gone through http://support.microsoft.com/?
kbid=328306 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||> I'm trying to connect from the ODBC Control Panel.
Why? And then what are you going to do with it once you've connected?
Typically this is used to create a DSN for some other application, like an
ASP page or VB app. In which case, you are much better off connecting
through OLEDB. I can't tell you how to do that until you provide more
information.
> Yes, I have through the 328306 article and haven't found
> any resolutions.
Can you tell us what you tried, with regard to each article listed at that
link? I'm guessing you didn't inspect every single article listed there,
since it has been less than an hour, and you'd have to be Number 5 from
Short Circuit to have read all of the content already.
> Would the easiest thing to do be revert back to Windows
> 2000 when this all worked correctly?
No, that's silly. Windows XP is not to blame; you have a configuration
issue, that's all. If you tell us everything you tried regarding 328306,
you will leave the remaining potential options.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I need the DSN to run Crystal Reports.
As for KB #328306, I tried everything under the Server-
Related Causes. The protocols are all ok, port 1433 is
the same on each machine, and tried giving the server a
new alias.
As for the Client/App-Related Causes, the protocols don't
change, no network adaptor issues, not worried about MDAC
connections, and Named Pipes don't apply.
There was nothing applicable under the Network Causes as
everything is run locally.
>--Original Message--
>> I'm trying to connect from the ODBC Control Panel.
>Why? And then what are you going to do with it once
you've connected?
>Typically this is used to create a DSN for some other
application, like an
>ASP page or VB app. In which case, you are much better
off connecting
>through OLEDB. I can't tell you how to do that until you
provide more
>information.
>> Yes, I have through the 328306 article and haven't found
>> any resolutions.
>Can you tell us what you tried, with regard to each
article listed at that
>link? I'm guessing you didn't inspect every single
article listed there,
>since it has been less than an hour, and you'd have to be
Number 5 from
>Short Circuit to have read all of the content already.
>> Would the easiest thing to do be revert back to Windows
>> 2000 when this all worked correctly?
>No, that's silly. Windows XP is not to blame; you have a
configuration
>issue, that's all. If you tell us everything you tried
regarding 328306,
>you will leave the remaining potential options.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||So what name are you using to refer to the local server? Have you tried
"LOCALHOST", "(LOCAL)", "127.0.0.1", the actual server name, the actual IP
address?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:199ec01c44d7f$b2851cf0$a301280a@.phx.gbl...
> I need the DSN to run Crystal Reports.
> As for KB #328306, I tried everything under the Server-
> Related Causes. The protocols are all ok, port 1433 is
> the same on each machine, and tried giving the server a
> new alias.
> As for the Client/App-Related Causes, the protocols don't
> change, no network adaptor issues, not worried about MDAC
> connections, and Named Pipes don't apply.
> There was nothing applicable under the Network Causes as
> everything is run locally.
> >--Original Message--
> >> I'm trying to connect from the ODBC Control Panel.
> >
> >Why? And then what are you going to do with it once
> you've connected?
> >Typically this is used to create a DSN for some other
> application, like an
> >ASP page or VB app. In which case, you are much better
> off connecting
> >through OLEDB. I can't tell you how to do that until you
> provide more
> >information.
> >
> >> Yes, I have through the 328306 article and haven't found
> >> any resolutions.
> >
> >Can you tell us what you tried, with regard to each
> article listed at that
> >link? I'm guessing you didn't inspect every single
> article listed there,
> >since it has been less than an hour, and you'd have to be
> Number 5 from
> >Short Circuit to have read all of the content already.
> >
> >> Would the easiest thing to do be revert back to Windows
> >> 2000 when this all worked correctly?
> >
> >No, that's silly. Windows XP is not to blame; you have a
> configuration
> >issue, that's all. If you tell us everything you tried
> regarding 328306,
> >you will leave the remaining potential options.
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >.
> >|||The actual server name is what I'm using.
>--Original Message--
>So what name are you using to refer to the local server?
Have you tried
>"LOCALHOST", "(LOCAL)", "127.0.0.1", the actual server
name, the actual IP
>address?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message
>news:199ec01c44d7f$b2851cf0$a301280a@.phx.gbl...
>> I need the DSN to run Crystal Reports.
>> As for KB #328306, I tried everything under the Server-
>> Related Causes. The protocols are all ok, port 1433 is
>> the same on each machine, and tried giving the server a
>> new alias.
>> As for the Client/App-Related Causes, the protocols
don't
>> change, no network adaptor issues, not worried about
MDAC
>> connections, and Named Pipes don't apply.
>> There was nothing applicable under the Network Causes as
>> everything is run locally.
>> >--Original Message--
>> >> I'm trying to connect from the ODBC Control Panel.
>> >
>> >Why? And then what are you going to do with it once
>> you've connected?
>> >Typically this is used to create a DSN for some other
>> application, like an
>> >ASP page or VB app. In which case, you are much better
>> off connecting
>> >through OLEDB. I can't tell you how to do that until
you
>> provide more
>> >information.
>> >
>> >> Yes, I have through the 328306 article and haven't
found
>> >> any resolutions.
>> >
>> >Can you tell us what you tried, with regard to each
>> article listed at that
>> >link? I'm guessing you didn't inspect every single
>> article listed there,
>> >since it has been less than an hour, and you'd have to
be
>> Number 5 from
>> >Short Circuit to have read all of the content already.
>> >
>> >> Would the easiest thing to do be revert back to
Windows
>> >> 2000 when this all worked correctly?
>> >
>> >No, that's silly. Windows XP is not to blame; you
have a
>> configuration
>> >issue, that's all. If you tell us everything you tried
>> regarding 328306,
>> >you will leave the remaining potential options.
>> >
>> >--
>> >http://www.aspfaq.com/
>> >(Reverse address to reply.)
>> >
>> >
>> >.
>> >
>
>.
>|||So, try the others...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:199f801c44d81$97deaf90$a301280a@.phx.gbl...
> The actual server name is what I'm using.
> >--Original Message--
> >So what name are you using to refer to the local server?
> Have you tried
> >"LOCALHOST", "(LOCAL)", "127.0.0.1", the actual server
> name, the actual IP
> >address?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Mike" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:199ec01c44d7f$b2851cf0$a301280a@.phx.gbl...
> >> I need the DSN to run Crystal Reports.
> >>
> >> As for KB #328306, I tried everything under the Server-
> >> Related Causes. The protocols are all ok, port 1433 is
> >> the same on each machine, and tried giving the server a
> >> new alias.
> >>
> >> As for the Client/App-Related Causes, the protocols
> don't
> >> change, no network adaptor issues, not worried about
> MDAC
> >> connections, and Named Pipes don't apply.
> >>
> >> There was nothing applicable under the Network Causes as
> >> everything is run locally.
> >>
> >> >--Original Message--
> >> >> I'm trying to connect from the ODBC Control Panel.
> >> >
> >> >Why? And then what are you going to do with it once
> >> you've connected?
> >> >Typically this is used to create a DSN for some other
> >> application, like an
> >> >ASP page or VB app. In which case, you are much better
> >> off connecting
> >> >through OLEDB. I can't tell you how to do that until
> you
> >> provide more
> >> >information.
> >> >
> >> >> Yes, I have through the 328306 article and haven't
> found
> >> >> any resolutions.
> >> >
> >> >Can you tell us what you tried, with regard to each
> >> article listed at that
> >> >link? I'm guessing you didn't inspect every single
> >> article listed there,
> >> >since it has been less than an hour, and you'd have to
> be
> >> Number 5 from
> >> >Short Circuit to have read all of the content already.
> >> >
> >> >> Would the easiest thing to do be revert back to
> Windows
> >> >> 2000 when this all worked correctly?
> >> >
> >> >No, that's silly. Windows XP is not to blame; you
> have a
> >> configuration
> >> >issue, that's all. If you tell us everything you tried
> >> regarding 328306,
> >> >you will leave the remaining potential options.
> >> >
> >> >--
> >> >http://www.aspfaq.com/
> >> >(Reverse address to reply.)
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

odbc connection call failed

I moved a sql 2000 database to a sql 2005 server. I have a front end in access 2003. I manually created an ODBC data source system DSN using the ODBC Data Source Administrator just like it was on the other server. I can connect fine but users are saying their getting an odbc call fail. I created the connection and relinked the tables to the new server with the moved database. I completed the same task with another database and the user can connect fine. What could be the problem?

You will have to look at the additional information of the ODBC call / error message. I guess they are not priviledged to access the database or even connct to the server.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

ODBC CONNECTION

I have an app which using Access front end and SQL server backend and ODBC
connection.
My app may access the SQL server backend database without ODBC
configuration.
Is it possible? If yes, where I can investigate the issue.
Any information is great appreciated,
Souris,
By "without ODBC" configuration, do you mean without a DSN?
Yes that's possible. You can find a lot of articles if you
search on dsn-less. This is what's returned from the
Knowledge Base:
http://support.microsoft.com/search/...px?qu=dsn-less
You can find examples of connection strings at:
http://www.carlprothman.net/Default.aspx?tabid=80
-Sue
On Thu, 12 May 2005 00:16:45 -0400, "souris"
<soukkris@.viddotron.com> wrote:

>I have an app which using Access front end and SQL server backend and ODBC
>connection.
>My app may access the SQL server backend database without ODBC
>configuration.
>Is it possible? If yes, where I can investigate the issue.
>Any information is great appreciated,
>Souris,
>

Friday, March 30, 2012

ODBC Error

ODBC Driver to Connect With IBM AS/400 System

ODBC Driver to Connect With IBM AS/400 System

ODBC driver problem occurs on table create

Wednesday, March 28, 2012

ODBC Driver for SQLServer Everywhere/Compact from Access

ODBC Driver for AS400

ODBC Driver for Access and AS/400

ODBC Driver for Access and AS/400

Monday, March 26, 2012

ODBC database slows down over time

Friday, March 23, 2012

ODBC Connection using Alternate windows credentials

ODBC Connection using Alternate windows credentials

ODBC connection to SQL server failing

odbc connection to access

Wednesday, March 21, 2012

ODBC Connection in UNIX

ODBC connection failure

ODBC Connection failure

ODBC connection failure

ODBC connection failure

odbc connection call failed

ODBC CONNECTION

Objects related to DB

Blog Archive

About Me