SQL Server Connection String

**MGFoster** · Nov 12 '05, 04:03 PM

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/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
ri1kgheRkK66JYC 0TiYLs9zE
=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=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
>
>
>
>
>[/color]

**Fred Zuckerman** · Nov 12 '05, 04:04 PM

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@newsr ead4.news.pas.e arthlink.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/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
> ri1kgheRkK66JYC 0TiYLs9zE
> =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=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
> >
> >
> >
> >
> >[/color]
>[/color]

**Jerry Boone** · Nov 12 '05, 04:04 PM

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.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" <ZuckermanF@sbc global.net> wrote in message
news:YHQpb.1531 6$9K2.7465@news svr27.news.prod igy.com...[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@newsr ead4.news.pas.e arthlink.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/AwUBP6b7E4echKq OuFEgEQJBYgCgjt D4FIyvYmrQw5JjG WpWMg5r1D0AoKyV
> > ri1kgheRkK66JYC 0TiYLs9zE
> > =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=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking"[color=blue][color=green][color=darkred]
> > > 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
> > >
> > >
> > >
> > >
> > >[/color]
> >[/color]
>
>[/color]

**Fred Zuckerman** · Nov 12 '05, 04:04 PM

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.b iz.nospam> wrote in message
news:TgSpb.1564 $ip3.1088@newss vr22.news.prodi gy.com...[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.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",[/color]
CurrentProject. Connection,[color=blue]
> adOpenStatic, adLockOptimisti c, 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@sbc global.net> wrote in message
> news:YHQpb.1531 6$9K2.7465@news svr27.news.prod igy.com...[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@newsr ead4.news.pas.e arthlink.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/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[/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=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking"[color=green][color=darkred]
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**Jerry Boone** · Nov 12 '05, 04:05 PM

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 .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" <ZuckermanF@sbc global.net> wrote in message
news:PISpb.1625 5$xq3.1193@news svr27.news.prod igy.com...[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.b iz.nospam> wrote in message
> news:TgSpb.1564 $ip3.1088@newss vr22.news.prodi gy.com...[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.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",[/color]
> CurrentProject. Connection,[color=green]
> > adOpenStatic, adLockOptimisti c, 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@sbc global.net> wrote in message
> > news:YHQpb.1531 6$9K2.7465@news svr27.news.prod igy.com...[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@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[/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/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[/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=SQ LServer;Server= SQL04/INST04;Database =SQLEmployeeTra cking"[color=darkred]
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

SQL Server Connection String

SQL Server Connection String

Comment

Comment

Comment

Comment

Comment