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=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

re: SQL Server Connection String

-----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:[color=blue]
> 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
>
>
>
>
>[/color]

re: SQL Server Connection String

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:pWCpb.6005$qh2.2930@newsread4.news.pas.earthl ink.net...[color=blue]
> -----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:[color=green]
> > 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[/color][/color]
DSN[color=blue][color=green]
> > file and a DSN-less connection string and I have found the following[/color][/color]
issue.[color=blue][color=green]
> > 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[/color][/color]
unique[color=blue][color=green]
> > 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[/color][/color]
specifying[color=blue][color=green]
> > the unique key. I have seached around in the A2k help files and in[/color][/color]
Google[color=blue][color=green]
> > but haven't found a listing of all of the possible parameters. Here is[/color][/color]
my[color=blue][color=green]
> > 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
> >
> >
> >
> >
> >[/color]
>[/color]

re: SQL Server Connection String

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" <ZuckermanF@sbcglobal.net> wrote in message
news:YHQpb.15316$9K2.7465@newssvr27.news.prodigy.c om...[color=blue]
> 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:pWCpb.6005$qh2.2930@newsread4.news.pas.earthl ink.net...[color=green]
> > -----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:[color=darkred]
> > > Hello All,
> > > After reading in this group about the preference for connecting to a[/color][/color][/color]
SQL[color=blue][color=green][color=darkred]
> > > Server using a connection string instead of a DSN file, I have done[/color][/color][/color]
just[color=blue][color=green][color=darkred]
> > > that.
> > >
> > > BUT, I cannot update my data. I have gone back and forth between using[/color][/color][/color]
a[color=blue]
> DSN[color=green][color=darkred]
> > > file and a DSN-less connection string and I have found the following[/color][/color]
> issue.[color=green][color=darkred]
> > > When I utilize the DSN file to create the link, there is a step where[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > asks me to identify a unique key for the file. If I do not identify a[/color][/color]
> unique[color=green][color=darkred]
> > > 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[/color][/color]
> specifying[color=green][color=darkred]
> > > the unique key. I have seached around in the A2k help files and in[/color][/color]
> Google[color=green][color=darkred]
> > > but haven't found a listing of all of the possible parameters. Here[/color][/color][/color]
is[color=blue]
> my[color=green][color=darkred]
> > > current code:
> > >
> > > strTable = "Roster"
> > > strConnect =
> > >[/color][/color][/color]
"ODBC;Driver=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking"[color=blue][color=green][color=darkred]
> > > 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
> > >
> > >
> > >
> > >
> > >[/color]
> >[/color]
>
>[/color]

re: SQL Server Connection String

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" <jerry@antech.biz.nospam> wrote in message
news:TgSpb.1564$ip3.1088@newssvr22.news.prodigy.co m...[color=blue]
> 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",[/color]
CurrentProject.Connection,[color=blue]
> adOpenStatic, adLockOptimistic, adCmdText
>
> Good luck!
>
> ps: I recommend you check out one of William Vaughn's ADO books - they[/color]
are[color=blue]
> all great and really open up a whole new world of programming data.
>
>
>
> "Fred Zuckerman" <ZuckermanF@sbcglobal.net> wrote in message
> news:YHQpb.15316$9K2.7465@newssvr27.news.prodigy.c om...[color=green]
> > 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:pWCpb.6005$qh2.2930@newsread4.news.pas.earthl ink.net...[color=darkred]
> > > -----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[/color][/color]
> SQL[color=green][color=darkred]
> > > > Server using a connection string instead of a DSN file, I have done[/color][/color]
> just[color=green][color=darkred]
> > > > that.
> > > >
> > > > BUT, I cannot update my data. I have gone back and forth between[/color][/color][/color]
using[color=blue]
> a[color=green]
> > DSN[color=darkred]
> > > > file and a DSN-less connection string and I have found the following[/color]
> > issue.[color=darkred]
> > > > When I utilize the DSN file to create the link, there is a step[/color][/color][/color]
where[color=blue]
> it[color=green][color=darkred]
> > > > asks me to identify a unique key for the file. If I do not identify[/color][/color][/color]
a[color=blue][color=green]
> > unique[color=darkred]
> > > > key, then I am unable to update data.
> > > >
> > > > My question is whether it is possible to identify the unique key in[/color][/color][/color]
my[color=blue][color=green][color=darkred]
> > > > connection string? I assume that there is another parameter for[/color]
> > specifying[color=darkred]
> > > > the unique key. I have seached around in the A2k help files and in[/color]
> > Google[color=darkred]
> > > > but haven't found a listing of all of the possible parameters. Here[/color][/color]
> is[color=green]
> > my[color=darkred]
> > > > current code:
> > > >
> > > > strTable = "Roster"
> > > > strConnect =
> > > >[/color][/color]
> "ODBC;Driver=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking"[color=green][color=darkred]
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: SQL Server Connection String

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" <ZuckermanF@sbcglobal.net> wrote in message
news:PISpb.16255$xq3.1193@newssvr27.news.prodigy.c om...[color=blue]
> Jerry,
> I am using a form (based upon the linked table) for updating the fields.[/color]
Are[color=blue]
> you suggesting that I utilize your code in the AfterUpdate event of every
> field? That seems inefficient.
> Thanks,
> Fred
>
>
> "Jerry Boone" <jerry@antech.biz.nospam> wrote in message
> news:TgSpb.1564$ip3.1088@newssvr22.news.prodigy.co m...[color=green]
> > You might want to get into using ADO recordsets or using sql statements[/color][/color]
to[color=blue][color=green]
> > update your data. I do this all day long and it's really the best way[/color][/color]
for[color=blue][color=green]
> > me.
> >
> > For instance, this is how you could update some data with little[/color][/color]
coding...[color=blue][color=green]
> >
> > 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",[/color]
> CurrentProject.Connection,[color=green]
> > adOpenStatic, adLockOptimistic, adCmdText
> >
> > Good luck!
> >
> > ps: I recommend you check out one of William Vaughn's ADO books - they[/color]
> are[color=green]
> > all great and really open up a whole new world of programming data.
> >
> >
> >
> > "Fred Zuckerman" <ZuckermanF@sbcglobal.net> wrote in message
> > news:YHQpb.15316$9K2.7465@newssvr27.news.prodigy.c om...[color=darkred]
> > > 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:pWCpb.6005$qh2.2930@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[/color][/color][/color]
record[color=blue][color=green][color=darkred]
> > > > Access won't "know" which record in the SQL table use updated.
> > > >
> > > > See the SQL server books on-line article "timestamp data type" in[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > "Transact SQL Reference" book. There's a help article in Access[/color][/color][/color]
about[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
a[color=blue][color=green]
> > SQL[color=darkred]
> > > > > Server using a connection string instead of a DSN file, I have[/color][/color][/color]
done[color=blue][color=green]
> > just[color=darkred]
> > > > > that.
> > > > >
> > > > > BUT, I cannot update my data. I have gone back and forth between[/color][/color]
> using[color=green]
> > a[color=darkred]
> > > DSN
> > > > > file and a DSN-less connection string and I have found the[/color][/color][/color]
following[color=blue][color=green][color=darkred]
> > > issue.
> > > > > When I utilize the DSN file to create the link, there is a step[/color][/color]
> where[color=green]
> > it[color=darkred]
> > > > > asks me to identify a unique key for the file. If I do not[/color][/color][/color]
identify[color=blue]
> a[color=green][color=darkred]
> > > unique
> > > > > key, then I am unable to update data.
> > > > >
> > > > > My question is whether it is possible to identify the unique key[/color][/color][/color]
in[color=blue]
> my[color=green][color=darkred]
> > > > > 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.[/color][/color][/color]
Here[color=blue][color=green]
> > is[color=darkred]
> > > my
> > > > > current code:
> > > > >
> > > > > strTable = "Roster"
> > > > > strConnect =
> > > > >[/color]
> > "ODBC;Driver=SQLServer;Server=SQL04/INST04;Database=SQLEmployeeTracking"[color=darkred]
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]