Connection string, avoiding the use of ODBC

[Guest]

I have an application I deploy that makes a number of internal SQL calls for
all sorts of purposes. In order to isolate the connection, I used a global
CONST, cnn, that reads:

"ODBC;Database=testing;UID=;PWD=;DSN=SQL Server"

The problem with this approach is that it requires me to set up a SQL Server
DNS on every new machine. Since the hardware guys are there at odd hours,
this invariably leaves new machines without a link in the morning.

Reading over the excellent wiki article on MDAC suggests that this isn't
really the way to do this anyway, and that I should be using a OLE DB link
(is there such a thing in these strings?).

Can anyone recommend a better way of doing this? The connection is the same
one that is being used by the Access application itself, can I use that in my
rstThingy.open "SOMETHING", cnn? Or should I change the connection string and
continue to use that method?

Maury

 

[Scott McDaniel]

I have an application I deploy that makes a number of internal SQL calls for
all sorts of purposes. In order to isolate the connection, I used a global
CONST, cnn, that reads:

"ODBC;Database=testing;UID=;PWD=;DSN=SQL Server"

The problem with this approach is that it requires me to set up a SQL Server
DNS on every new machine. Since the hardware guys are there at odd hours,
this invariably leaves new machines without a link in the morning.

Reading over the excellent wiki article on MDAC suggests that this isn't
really the way to do this anyway, and that I should be using a OLE DB link
(is there such a thing in these strings?).

You're apparently building an ADODB.Connection from this? If so, you can use one of the samples here:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

as an example. This is the method I've used for years and requires no installation on the enduser machine, assuming MDAC
has been installed (and that's pretty much a sure thing with any Windows 2000+ machine).

Can anyone recommend a better way of doing this? The connection is the same
one that is being used by the Access application itself, can I use that in my
rstThingy.open "SOMETHING", cnn? Or should I change the connection string and
continue to use that method?

You can get the current Access project connection like this:

CurrentProject.Connection

So you could do this:

rst.Open "SQL", CurrentProject.connection

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

CurrentProject.Connection

So you could do this:

rst.Open "SQL", CurrentProject.connection

Oh, I like that. Thanks!

Maury

 

[Guest]

rst.Open "SQL", CurrentProject.connection

Darn, this didn't work. There might be a way to fix it, though.

Basically I like the user to be able to back out of edits easily. So
generally I copy a row into a temp table, and bind the form to that temp. In
order to do this, I have to read the primary key, which used to work fine.
When I try the solution above, I get an error that states "Cannot retrieve
the ID for newly inserted rows".

I might be able to code around this in this particular case (which is a bit
of an odd one in any case), but I'm a bit worried that all the other
instances will fail too. So in the meantime I'm turning this off while I
experiment.

Maury

 

[Susie DBA [MSFT]]

seriously

if you want to connect to SQL Server, then just use Access Data
Projects
FILE, CONNECTION-- and you're done

crap like connection strings is 10 years obsolete


life is SO much easier without the linked table manager and all that
other crap

 

[Susie DBA [MSFT]]

what do you mean it didn't work?

are you retardard and still using DAO?
DAO hasn't been incldued with Office, Windows or MDAC for a decade

stick a cork in it and use ADO

 

[Susie DBA [MSFT]]

in SQL Server, you can use @@Identity in order to return the row
number for the newly inserted row.
In SQL 2005; it's even better; you can return a list of ALL of the new
row numbers for example

 

[Tony Toews [MVP]]

seriously

if you want to connect to SQL Server, then just use Access Data
Projects
FILE, CONNECTION-- and you're done

cr*p like connection strings is 10 years obsolete


life is SO much easier without the linked table manager and all that
other cr*p

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Aaron Kempf]

what do you mean this didn't work?

are you trying to use DAO recordsets?

DAO is obsolete, it hasn't been included with Windows, MDAC or Office for a
decade

 

[Susie DBA [MSFT]]

note that Tony is neither MOST, VALUABLE -OR- A PROFESSIONAL

he is a MDB cry baby


'oh, but it's too hard to use sql server'


rofl


your crack addict loser MDB development is obsolete, kid