Connection string, avoiding the use of ODBC

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?).
Click to expand...

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?
Click to expand...

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
 
Scott McDaniel said:
rst.Open "SQL", CurrentProject.connection
Click to expand...

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
 
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
 
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
 
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
 
S u s ie D B A said:
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
Click to expand...

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
 
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
 
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
 
Back
Top