By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,010 Members | 2,774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,010 IT Pros & Developers. It's quick & easy.

SQL Server Connection String

P: n/a
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=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking"
set db = CurrentDB
set tdf = db.CreateTableDef(strTable)
tdf.Connect = strConnect
tdf.SourceTableName = strTable
db.TableDefs.Append tdf

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

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
-----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/AwUBP6b7E4echKqOuFEgEQJBYgCgjtD4FIyvYmrQw5JjGWpWMg 5r1D0AoKyV
ri1kgheRkK66JYC0TiYLs9zE
=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=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking"
set db = CurrentDB
set tdf = db.CreateTableDef(strTable)
tdf.Connect = strConnect
tdf.SourceTableName = strTable
db.TableDefs.Append tdf

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



Nov 12 '05 #2

P: n/a
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*****************@newsread4.news.pas.earthl ink.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/AwUBP6b7E4echKqOuFEgEQJBYgCgjtD4FIyvYmrQw5JjGWpWMg 5r1D0AoKyV
ri1kgheRkK66JYC0TiYLs9zE
=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=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking"
set db = CurrentDB
set tdf = db.CreateTableDef(strTable)
tdf.Connect = strConnect
tdf.SourceTableName = strTable
db.TableDefs.Append tdf

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


Nov 12 '05 #3

P: n/a
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.Connection
Set Cnn = New ADODB.Connection
cnn.open "Driver={SQL Server};" _
& "Server=SomeSqlServer;" _
& "Database="SomeDatabase;" _
& "UID=yourUsername;" _
& "PWD=YourPassword;"
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,
adLockOptimistic, adCmdText
do until rs.eof
rs.fields("myField") = "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, adLockOptimistic, 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********@sbcglobal.net> wrote in message
news:YH******************@newssvr27.news.prodigy.c om...
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*****************@newsread4.news.pas.earthl ink.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/AwUBP6b7E4echKqOuFEgEQJBYgCgjtD4FIyvYmrQw5JjGWpWMg 5r1D0AoKyV
ri1kgheRkK66JYC0TiYLs9zE
=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=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking" set db = CurrentDB
set tdf = db.CreateTableDef(strTable)
tdf.Connect = strConnect
tdf.SourceTableName = strTable
db.TableDefs.Append tdf

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



Nov 12 '05 #4

P: n/a
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.biz.nospam> wrote in message
news:Tg*****************@newssvr22.news.prodigy.co m...
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.Connection
Set Cnn = New ADODB.Connection
cnn.open "Driver={SQL Server};" _
& "Server=SomeSqlServer;" _
& "Database="SomeDatabase;" _
& "UID=yourUsername;" _
& "PWD=YourPassword;"
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,
adLockOptimistic, adCmdText
do until rs.eof
rs.fields("myField") = "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, adLockOptimistic, 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********@sbcglobal.net> wrote in message
news:YH******************@newssvr27.news.prodigy.c om...
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*****************@newsread4.news.pas.earthl ink.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/AwUBP6b7E4echKqOuFEgEQJBYgCgjtD4FIyvYmrQw5JjGWpWMg 5r1D0AoKyV
ri1kgheRkK66JYC0TiYLs9zE
=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=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking" > set db = CurrentDB
> set tdf = db.CreateTableDef(strTable)
> tdf.Connect = strConnect
> tdf.SourceTableName = strTable
> db.TableDefs.Append tdf
>
> Thank you in advance,
> Fred Zuckerman
> San Diego, CA, USA
>
>
>
>
>



Nov 12 '05 #5

P: n/a
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.BaseConnectionString
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********@sbcglobal.net> wrote in message
news:PI******************@newssvr27.news.prodigy.c om...
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.biz.nospam> wrote in message
news:Tg*****************@newssvr22.news.prodigy.co m...
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.Connection
Set Cnn = New ADODB.Connection
cnn.open "Driver={SQL Server};" _
& "Server=SomeSqlServer;" _
& "Database="SomeDatabase;" _
& "UID=yourUsername;" _
& "PWD=YourPassword;"
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,
adLockOptimistic, adCmdText
do until rs.eof
rs.fields("myField") = "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, adLockOptimistic, 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********@sbcglobal.net> wrote in message
news:YH******************@newssvr27.news.prodigy.c om...
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*****************@newsread4.news.pas.earthl ink.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/AwUBP6b7E4echKqOuFEgEQJBYgCgjtD4FIyvYmrQw5JjGWpWMg 5r1D0AoKyV
> ri1kgheRkK66JYC0TiYLs9zE
> =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=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking"
> > set db = CurrentDB
> > set tdf = db.CreateTableDef(strTable)
> > tdf.Connect = strConnect
> > tdf.SourceTableName = strTable
> > db.TableDefs.Append tdf
> >
> > Thank you in advance,
> > Fred Zuckerman
> > San Diego, CA, USA
> >
> >
> >
> >
> >
>



Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.