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
5  8706 
-----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
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
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
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
>
>
>
>
>
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
> >
> >
> >
> >
> >
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
