473,574 Members | 2,927 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Server Connection String

Hello All,
After reading in this group about the preference for connecting to a SQL
Server using a connection string instead of a DSN file, I have done just
that.

BUT, I cannot update my data. I have gone back and forth between using a DSN
file and a DSN-less connection string and I have found the following issue.
When I utilize the DSN file to create the link, there is a step where it
asks me to identify a unique key for the file. If I do not identify a unique
key, then I am unable to update data.

My question is whether it is possible to identify the unique key in my
connection string? I assume that there is another parameter for specifying
the unique key. I have seached around in the A2k help files and in Google
but haven't found a listing of all of the possible parameters. Here is my
current code:

strTable = "Roster"
strConnect =
"ODBC;Driver=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking"
set db = CurrentDB
set tdf = db.CreateTableD ef(strTable)
tdf.Connect = strConnect
tdf.SourceTable Name = strTable
db.TableDefs.Ap pend tdf

Thank you in advance,
Fred Zuckerman
San Diego, CA, USA

Nov 12 '05 #1
5 8734
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The easiest thing to do is create an Identity column in the MS SQL
Server table. You could also create a TimeStamp column in the SQL
Server table. Access needs one, or the other, to determine if a
record has been updated. If there isn't a unique column(s) per record
Access won't "know" which record in the SQL table use updated.

See the SQL server books on-line article "timestamp data type" in the
"Transact SQL Reference" book. There's a help article in Access about
unique columns, but I can't find it in the A2K "non-" help files I
have available now.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
ri1kgheRkK66JYC 0TiYLs9zE
=gEkc
-----END PGP SIGNATURE-----
Fred Zuckerman wrote:
Hello All,
After reading in this group about the preference for connecting to a SQL
Server using a connection string instead of a DSN file, I have done just
that.

BUT, I cannot update my data. I have gone back and forth between using a DSN
file and a DSN-less connection string and I have found the following issue.
When I utilize the DSN file to create the link, there is a step where it
asks me to identify a unique key for the file. If I do not identify a unique
key, then I am unable to update data.

My question is whether it is possible to identify the unique key in my
connection string? I assume that there is another parameter for specifying
the unique key. I have seached around in the A2k help files and in Google
but haven't found a listing of all of the possible parameters. Here is my
current code:

strTable = "Roster"
strConnect =
"ODBC;Driver=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking"
set db = CurrentDB
set tdf = db.CreateTableD ef(strTable)
tdf.Connect = strConnect
tdf.SourceTable Name = strTable
db.TableDefs.Ap pend tdf

Thank you in advance,
Fred Zuckerman
San Diego, CA, USA



Nov 12 '05 #2
Thank you for your comments.
The SQL table already has a unique key field and I know what it is.
I just do not know how to put that info in the connection string.
Fred

"MGFoster" <me@privacy.com > wrote in message
news:pW******** *********@newsr ead4.news.pas.e arthlink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The easiest thing to do is create an Identity column in the MS SQL
Server table. You could also create a TimeStamp column in the SQL
Server table. Access needs one, or the other, to determine if a
record has been updated. If there isn't a unique column(s) per record
Access won't "know" which record in the SQL table use updated.

See the SQL server books on-line article "timestamp data type" in the
"Transact SQL Reference" book. There's a help article in Access about
unique columns, but I can't find it in the A2K "non-" help files I
have available now.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
ri1kgheRkK66JYC 0TiYLs9zE
=gEkc
-----END PGP SIGNATURE-----
Fred Zuckerman wrote:
Hello All,
After reading in this group about the preference for connecting to a SQL
Server using a connection string instead of a DSN file, I have done just
that.

BUT, I cannot update my data. I have gone back and forth between using a DSN file and a DSN-less connection string and I have found the following issue. When I utilize the DSN file to create the link, there is a step where it
asks me to identify a unique key for the file. If I do not identify a unique key, then I am unable to update data.

My question is whether it is possible to identify the unique key in my
connection string? I assume that there is another parameter for specifying the unique key. I have seached around in the A2k help files and in Google but haven't found a listing of all of the possible parameters. Here is my current code:

strTable = "Roster"
strConnect =
"ODBC;Driver=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking"
set db = CurrentDB
set tdf = db.CreateTableD ef(strTable)
tdf.Connect = strConnect
tdf.SourceTable Name = strTable
db.TableDefs.Ap pend tdf

Thank you in advance,
Fred Zuckerman
San Diego, CA, USA


Nov 12 '05 #3
You might want to get into using ADO recordsets or using sql statements to
update your data. I do this all day long and it's really the best way for
me.

For instance, this is how you could update some data with little coding...

Dim Cnn as ADODB.Connectio n
Set Cnn = New ADODB.Connectio n
cnn.open "Driver={SQ L Server};" _
& "Server=SomeSql Server;" _
& "Database="Some Database;" _
& "UID=yourUserna me;" _
& "PWD=YourPasswo rd;"
Cnn.Execute ("Update myTable SET myField = '12345'" Where DateField =
'11/1/03')

Or you could open a recordset and do it this way (using the cnn opened
above)...

Dim rs as ADODB.Recordset
Set rs = new ADODB.Recordset
rs.open "Select myField, DateField From MyTable", Cnn, adOpenStatic,
adLockOptimisti c, adCmdText
do until rs.eof
rs.fields("myFi eld") = "12345"
rs.update
rs.movenext
loop

Recordsets have filtering, sorting, and all sorts of stuff. If you are
running an ADP type project rather than MDB, you can actually just
reference:

CurrentProject. Connection

It returns the current open connection and you can use it like this...

rs.open "Select myField, DateField From MyTable", CurrentProject. Connection,
adOpenStatic, adLockOptimisti c, adCmdText

Good luck!

ps: I recommend you check out one of William Vaughn's ADO books - they are
all great and really open up a whole new world of programming data.

"Fred Zuckerman" <Zu********@sbc global.net> wrote in message
news:YH******** **********@news svr27.news.prod igy.com...
Thank you for your comments.
The SQL table already has a unique key field and I know what it is.
I just do not know how to put that info in the connection string.
Fred

"MGFoster" <me@privacy.com > wrote in message
news:pW******** *********@newsr ead4.news.pas.e arthlink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The easiest thing to do is create an Identity column in the MS SQL
Server table. You could also create a TimeStamp column in the SQL
Server table. Access needs one, or the other, to determine if a
record has been updated. If there isn't a unique column(s) per record
Access won't "know" which record in the SQL table use updated.

See the SQL server books on-line article "timestamp data type" in the
"Transact SQL Reference" book. There's a help article in Access about
unique columns, but I can't find it in the A2K "non-" help files I
have available now.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
ri1kgheRkK66JYC 0TiYLs9zE
=gEkc
-----END PGP SIGNATURE-----
Fred Zuckerman wrote:
Hello All,
After reading in this group about the preference for connecting to a SQL Server using a connection string instead of a DSN file, I have done just that.

BUT, I cannot update my data. I have gone back and forth between using a
DSN
file and a DSN-less connection string and I have found the following issue. When I utilize the DSN file to create the link, there is a step where
it asks me to identify a unique key for the file. If I do not identify a
unique key, then I am unable to update data.

My question is whether it is possible to identify the unique key in my
connection string? I assume that there is another parameter for specifying the unique key. I have seached around in the A2k help files and in Google but haven't found a listing of all of the possible parameters. Here is my
current code:

strTable = "Roster"
strConnect =

"ODBC;Driver=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking" set db = CurrentDB
set tdf = db.CreateTableD ef(strTable)
tdf.Connect = strConnect
tdf.SourceTable Name = strTable
db.TableDefs.Ap pend tdf

Thank you in advance,
Fred Zuckerman
San Diego, CA, USA



Nov 12 '05 #4
Jerry,
I am using a form (based upon the linked table) for updating the fields. Are
you suggesting that I utilize your code in the AfterUpdate event of every
field? That seems inefficient.
Thanks,
Fred
"Jerry Boone" <je***@antech.b iz.nospam> wrote in message
news:Tg******** *********@newss vr22.news.prodi gy.com...
You might want to get into using ADO recordsets or using sql statements to
update your data. I do this all day long and it's really the best way for
me.

For instance, this is how you could update some data with little coding...

Dim Cnn as ADODB.Connectio n
Set Cnn = New ADODB.Connectio n
cnn.open "Driver={SQ L Server};" _
& "Server=SomeSql Server;" _
& "Database="Some Database;" _
& "UID=yourUserna me;" _
& "PWD=YourPasswo rd;"
Cnn.Execute ("Update myTable SET myField = '12345'" Where DateField =
'11/1/03')

Or you could open a recordset and do it this way (using the cnn opened
above)...

Dim rs as ADODB.Recordset
Set rs = new ADODB.Recordset
rs.open "Select myField, DateField From MyTable", Cnn, adOpenStatic,
adLockOptimisti c, adCmdText
do until rs.eof
rs.fields("myFi eld") = "12345"
rs.update
rs.movenext
loop

Recordsets have filtering, sorting, and all sorts of stuff. If you are
running an ADP type project rather than MDB, you can actually just
reference:

CurrentProject. Connection

It returns the current open connection and you can use it like this...

rs.open "Select myField, DateField From MyTable", CurrentProject. Connection, adOpenStatic, adLockOptimisti c, adCmdText

Good luck!

ps: I recommend you check out one of William Vaughn's ADO books - they are all great and really open up a whole new world of programming data.

"Fred Zuckerman" <Zu********@sbc global.net> wrote in message
news:YH******** **********@news svr27.news.prod igy.com...
Thank you for your comments.
The SQL table already has a unique key field and I know what it is.
I just do not know how to put that info in the connection string.
Fred

"MGFoster" <me@privacy.com > wrote in message
news:pW******** *********@newsr ead4.news.pas.e arthlink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The easiest thing to do is create an Identity column in the MS SQL
Server table. You could also create a TimeStamp column in the SQL
Server table. Access needs one, or the other, to determine if a
record has been updated. If there isn't a unique column(s) per record
Access won't "know" which record in the SQL table use updated.

See the SQL server books on-line article "timestamp data type" in the
"Transact SQL Reference" book. There's a help article in Access about
unique columns, but I can't find it in the A2K "non-" help files I
have available now.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
ri1kgheRkK66JYC 0TiYLs9zE
=gEkc
-----END PGP SIGNATURE-----
Fred Zuckerman wrote:
> Hello All,
> After reading in this group about the preference for connecting to a SQL > Server using a connection string instead of a DSN file, I have done just > that.
>
> BUT, I cannot update my data. I have gone back and forth between using
a
DSN
> file and a DSN-less connection string and I have found the following issue.
> When I utilize the DSN file to create the link, there is a step
where it > asks me to identify a unique key for the file. If I do not identify
a
unique
> key, then I am unable to update data.
>
> My question is whether it is possible to identify the unique key in

my > connection string? I assume that there is another parameter for

specifying
> the unique key. I have seached around in the A2k help files and in

Google
> but haven't found a listing of all of the possible parameters. Here

is
my
> current code:
>
> strTable = "Roster"
> strConnect =
>

"ODBC;Driver=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking" > set db = CurrentDB
> set tdf = db.CreateTableD ef(strTable)
> tdf.Connect = strConnect
> tdf.SourceTable Name = strTable
> db.TableDefs.Ap pend tdf
>
> Thank you in advance,
> Fred Zuckerman
> San Diego, CA, USA
>
>
>
>
>



Nov 12 '05 #5
I see - sorry about that... (sigh)

I'm not sure what type of connection string a linked table is able to use.

Hmm... But I would let an Access Project build the sql server connection
string for me:

Start Access2k or AXP and do a new "Project" (not mdb).
Fill in server, user/pass, etc (lots of parameters available on
File-Connection)
Make a new form
Right click, Build Event
In the VB Editor click View-Immediate Window
Type this:
?CurrentProject .BaseConnection String
Press Enter

It will print a valid connection string on the next line - try using it in
your mdb linked table and see what happens? You can also change options in
that connection string in the File-Connection dialog on various pages. I am
not very sure that this will help you though. It sounds like you are having
a read-only problem with the tabledef - might be more properties to set
before assigning the tabledef? Sql Server would tell you if you had
permission problems so that's not it.

"Fred Zuckerman" <Zu********@sbc global.net> wrote in message
news:PI******** **********@news svr27.news.prod igy.com...
Jerry,
I am using a form (based upon the linked table) for updating the fields. Are you suggesting that I utilize your code in the AfterUpdate event of every
field? That seems inefficient.
Thanks,
Fred
"Jerry Boone" <je***@antech.b iz.nospam> wrote in message
news:Tg******** *********@newss vr22.news.prodi gy.com...
You might want to get into using ADO recordsets or using sql statements to
update your data. I do this all day long and it's really the best way for me.

For instance, this is how you could update some data with little coding...
Dim Cnn as ADODB.Connectio n
Set Cnn = New ADODB.Connectio n
cnn.open "Driver={SQ L Server};" _
& "Server=SomeSql Server;" _
& "Database="Some Database;" _
& "UID=yourUserna me;" _
& "PWD=YourPasswo rd;"
Cnn.Execute ("Update myTable SET myField = '12345'" Where DateField =
'11/1/03')

Or you could open a recordset and do it this way (using the cnn opened
above)...

Dim rs as ADODB.Recordset
Set rs = new ADODB.Recordset
rs.open "Select myField, DateField From MyTable", Cnn, adOpenStatic,
adLockOptimisti c, adCmdText
do until rs.eof
rs.fields("myFi eld") = "12345"
rs.update
rs.movenext
loop

Recordsets have filtering, sorting, and all sorts of stuff. If you are
running an ADP type project rather than MDB, you can actually just
reference:

CurrentProject. Connection

It returns the current open connection and you can use it like this...

rs.open "Select myField, DateField From MyTable", CurrentProject. Connection,
adOpenStatic, adLockOptimisti c, adCmdText

Good luck!

ps: I recommend you check out one of William Vaughn's ADO books - they

are
all great and really open up a whole new world of programming data.

"Fred Zuckerman" <Zu********@sbc global.net> wrote in message
news:YH******** **********@news svr27.news.prod igy.com...
Thank you for your comments.
The SQL table already has a unique key field and I know what it is.
I just do not know how to put that info in the connection string.
Fred

"MGFoster" <me@privacy.com > wrote in message
news:pW******** *********@newsr ead4.news.pas.e arthlink.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> The easiest thing to do is create an Identity column in the MS SQL
> Server table. You could also create a TimeStamp column in the SQL
> Server table. Access needs one, or the other, to determine if a
> record has been updated. If there isn't a unique column(s) per record > Access won't "know" which record in the SQL table use updated.
>
> See the SQL server books on-line article "timestamp data type" in the > "Transact SQL Reference" book. There's a help article in Access about > unique columns, but I can't find it in the A2K "non-" help files I
> have available now.
>
> MGFoster:::mgf
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
> ri1kgheRkK66JYC 0TiYLs9zE
> =gEkc
> -----END PGP SIGNATURE-----
>
>
> Fred Zuckerman wrote:
> > Hello All,
> > After reading in this group about the preference for connecting to a
SQL
> > Server using a connection string instead of a DSN file, I have
done just
> > that.
> >
> > BUT, I cannot update my data. I have gone back and forth between using
a
DSN
> > file and a DSN-less connection string and I have found the

following issue.
> > When I utilize the DSN file to create the link, there is a step

where
it
> > asks me to identify a unique key for the file. If I do not

identify a unique
> > key, then I am unable to update data.
> >
> > My question is whether it is possible to identify the unique key
in
my > > connection string? I assume that there is another parameter for
specifying
> > the unique key. I have seached around in the A2k help files and in
Google
> > but haven't found a listing of all of the possible parameters.

Here is
my
> > current code:
> >
> > strTable = "Roster"
> > strConnect =
> >

"ODBC;Driver=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking"
> > set db = CurrentDB
> > set tdf = db.CreateTableD ef(strTable)
> > tdf.Connect = strConnect
> > tdf.SourceTable Name = strTable
> > db.TableDefs.Ap pend tdf
> >
> > Thank you in advance,
> > Fred Zuckerman
> > San Diego, CA, USA
> >
> >
> >
> >
> >
>



Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3993
by: dekoffie | last post by:
Hello there, I'm trying to create a connection with an IMAP server which is secured through SSL. I can get the connection just fine, and I get a first respone from the server. But then I send out my login string, and it does not seem to arrive at the server, because I don't get an answer returned! After some time, I receive a message from...
12
1314
by: RKay | last post by:
I have a Win2k server running SQL Server 2000. On that box I built a working web service that pulls data from the database. One of the services available simply accepts an ado.net connection string to test if a connection can be made to the local server. I can call that web service from any box on my network, and with the correct connection...
1
2275
by: vighnesh | last post by:
Hi All I am dealing a project in ASP.NET in which I have to establish a connection to SQL Server 2000 database,where the database was located on a remote system. For this I have used SQLConnection object and specified the connetion string.But when it comes to establish a connection its giving me the Error that I. Connection string :...
9
8311
by: craig.overton | last post by:
All, I am currently developing an FTP class in VB.NET. It's kid tested, mother approved when trying to access an FTP Server on a Windows box meaning I can connect, run commands, upload and download a file no problem. My issues come when I try to use the same class with the same commands to access an FTP server on a UNIX box. I can connect...
14
7016
by: Marcus | last post by:
I have a function that simply returns TRUE if it can connect to a particular Sql Server 2005 express, or FALSE if it cannot. I am getting some strange error codes returned when the computer that sql server resides on is not reachable. The error is different depending on the connection string that I use. If I use the following connection...
5
1926
by: James | last post by:
Hello, I have written a simple logon page that redirects to another page when successful. All works fine on my computer but when I upload it I get the error message below. I have written it in ASP.net v2, and have put it on IIS v6 on windows server 2003. I think it may be something to do with connection string to the database,
5
3539
by: Cirene | last post by:
I just deployed my new ASP.NET (3.5 FW) site to the hosting company I'm using, webhost4life. NOTE: I HAVE deployed other SQL Server sites to the same account with no issues. Now I'm getting this error. Any idea why? Server Error in '/myuser4/MyWebApp' Application....
8
6324
by: Brett | last post by:
I wrote an ASP.NET application that queries a SQL Server database (on a different box from the web server) and displays the result in a GridView. The datasource for the GridView is a SQLDataSource. Just to get it to work, I hard-coded the username and password of a SQL Server account in the connectionstring in web.config. Once I confirmed...
10
4220
by: Elaine121 | last post by:
Hi i've been batteling for hours and can't seem to find the problem. When my server runs and I press the connect button the gui freezes until the client gui is terminated.. only then the gui becomes active again and displays the messages. Here is my server code: import java.io.*; import java.net.*; public class serverForm extends...
0
7805
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7726
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7817
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6454
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5301
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3752
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2245
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1341
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1060
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.