Issues Connecting to SQLServer

[Tom]

I’m trying to use a DSN-less connection to a SQLSERVER DB and am
experiencing problems. I can connect to all the tables I need and
most of the views. However, one view is giving me problems.

The view in question is a summary for use on a report – basically
totaling up hours and dollars from a bunch of detail records – in an
attempt to reduce the amount of data sent over the network. For some
unknown reason, I can’t set an index on the view, so whenever I try to
link to it programmatically I get the “Select Unique Record
Identifier” dialog box. I don’t want the end user to have to deal
with that.

So as a work around, I tried going to a pass-through query. Works
great, but since it’s a DSN-less connection I need to embed the
password in the ODBC connection string. That’s a non-starter.

I’d really rather not go to a DSN connection and changing the query to
do the totaling locally instead of on the server will likely end up
slowing things down unnecessarily.

So my questions are:
• How do I programmatically deal with the select unique record
identifier? If I can dismiss that programmatically, I should be good
to go.
• Alternately, how do I obscure the ODBC connection string on the pass-
through query?
• Any other ways to go about this?

Thanks

 

[Douglas J. Steele]

I'm confused as to why it's okay to have the password stored in the TableDef
object's Connect property but not in the QueryDef object's.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I’m trying to use a DSN-less connection to a SQLSERVER DB and am
experiencing problems. I can connect to all the tables I need and
most of the views. However, one view is giving me problems.

The view in question is a summary for use on a report – basically
totaling up hours and dollars from a bunch of detail records – in an
attempt to reduce the amount of data sent over the network. For some
unknown reason, I can’t set an index on the view, so whenever I try to
link to it programmatically I get the “Select Unique Record
Identifier” dialog box. I don’t want the end user to have to deal
with that.

So as a work around, I tried going to a pass-through query. Works
great, but since it’s a DSN-less connection I need to embed the
password in the ODBC connection string. That’s a non-starter.

I’d really rather not go to a DSN connection and changing the query to
do the totaling locally instead of on the server will likely end up
slowing things down unnecessarily.

So my questions are:
• How do I programmatically deal with the select unique record
identifier? If I can dismiss that programmatically, I should be good
to go.
• Alternately, how do I obscure the ODBC connection string on the pass-
through query?
• Any other ways to go about this?

Thanks

 

[Tom]

I'm confused as to why it's okay to have the password stored in the TableDef
object's Connect property but not in the QueryDef object's.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


I m trying to use a DSN-less connection to a SQLSERVER DB and am
experiencing problems.  I can connect to all the tables I need and
most of the views.  However, one view is giving me problems.

The view in question is a summary for use on a report basically
totaling up hours and dollars from a bunch of detail records in an
attempt to reduce the amount of data sent over the network.  For some
unknown reason, I can t set an index on the view, so whenever I try to
link to it programmatically I get the Select Unique Record
Identifier dialog box.  I don t want the end user to have to deal
with that.

So as a work around, I tried going to a pass-through query.  Works
great, but since it s a DSN-less connection I need to embed the
password in the ODBC connection string.  That s a non-starter.

I d really rather not go to a DSN connection and changing the query to
do the totaling locally instead of on the server will likely end up
slowing things down unnecessarily.

So my questions are:
How do I programmatically deal with the select unique record
identifier?  If I can dismiss that programmatically, I should be good
to go.
Alternately, how do I obscure the ODBC connection string on the pass-
through query?
Any other ways to go about this?

Thanks

Maybe its trival, but with it stored in the TableDef connect property,
you need to write code to find it (yes, I know its a trival piece of
code, but its sufficent to keep out the merely curious). With the
query, you can open the query and then open the property dialog box
and its staring you in the face.

Bascially, I just need to do enough not to tempt people...

 

[Stefan Hoffmann]

hi Tom,

So as a work around, I tried going to a pass-through query. Works
great, but since it’s a DSN-less connection I need to embed the
password in the ODBC connection string. That’s a non-starter.

You should never store the password. How do you control whether an
expirienced user types

?CurrentDb.TableDefs.Item(0).Connect

in the immediate window?

So my questions are:
• How do I programmatically deal with the select unique record
identifier? If I can dismiss that programmatically, I should be good
to go.

This window is normally not raise when relinking your tables by code.


mfG
--> stefan <--

 

[Douglas J. Steele]

Open the table in Design mode (ignore the message that it's "a linked table
with some properties that can't be modified") and then look at the
Properties tab. The Connect string shows up in the Description property.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm confused as to why it's okay to have the password stored in the
TableDef
object's Connect property but not in the QueryDef object's.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


I m trying to use a DSN-less connection to a SQLSERVER DB and am
experiencing problems. I can connect to all the tables I need and
most of the views. However, one view is giving me problems.

The view in question is a summary for use on a report basically
totaling up hours and dollars from a bunch of detail records in an
attempt to reduce the amount of data sent over the network. For some
unknown reason, I can t set an index on the view, so whenever I try to
link to it programmatically I get the Select Unique Record
Identifier dialog box. I don t want the end user to have to deal
with that.

So as a work around, I tried going to a pass-through query. Works
great, but since it s a DSN-less connection I need to embed the
password in the ODBC connection string. That s a non-starter.

I d really rather not go to a DSN connection and changing the query to
do the totaling locally instead of on the server will likely end up
slowing things down unnecessarily.

So my questions are:
How do I programmatically deal with the select unique record
identifier? If I can dismiss that programmatically, I should be good
to go.
Alternately, how do I obscure the ODBC connection string on the pass-
through query?
Any other ways to go about this?

Thanks

Maybe its trival, but with it stored in the TableDef connect property,
you need to write code to find it (yes, I know its a trival piece of
code, but its sufficent to keep out the merely curious). With the
query, you can open the query and then open the property dialog box
and its staring you in the face.

Bascially, I just need to do enough not to tempt people...

 

[Tom]

Open the table in Design mode (ignore the message that it's "a linked table
with some properties that can't be modified") and then look at the
Properties tab. The Connect string shows up in the Description property.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)











Maybe its trival, but with it stored in the TableDef connect property,
you need to write code to find it (yes, I know its a trival piece of
code, but its sufficent to keep out the merely curious).  With the
query, you can open the query and then open the property dialog box
and its staring you in the face.

Bascially, I just need to do enough not to tempt people...- Hide quoted text -

- Show quoted text -

Doug:
The password is stripped out for the table. Its not stripped out for
the query. So I'm back to not wanting to put the password where it is
*easily* found.

Tom

 

[Douglas J. Steele]

Any chance of switching to Trusted Connection? No SQL Server IDs to manage
then...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Open the table in Design mode (ignore the message that it's "a linked
table
with some properties that can't be modified") and then look at the
Properties tab. The Connect string shows up in the Description property.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)











Maybe its trival, but with it stored in the TableDef connect property,
you need to write code to find it (yes, I know its a trival piece of
code, but its sufficent to keep out the merely curious). With the
query, you can open the query and then open the property dialog box
and its staring you in the face.

Bascially, I just need to do enough not to tempt people...- Hide quoted
text -

- Show quoted text -

Doug:
The password is stripped out for the table. Its not stripped out for
the query. So I'm back to not wanting to put the password where it is
*easily* found.

Tom

 

[Tom]

hi Tom,

On 10.12.2009 18:31, Tom wrote:> So as a work around, I tried going to a pass-through query.  Works

You should never store the password. How do you control whether an
expirienced user types

   ?CurrentDb.TableDefs.Item(0).Connect

in the immediate window?


This window is normally not raise when relinking your tables by code.

mfG
--> stefan <--

Stefan:

See below:

How do you control whether an expirienced user types?CurrentDb.TableDefs.Item(0).Connect

I can't - but I'm not really trying to. All I'm really trying to do
is prevent the password from being displayed to the merely curious.

This window is normally not raise when relinking your tables by code.

I'm using the code below to relink and I see it every time it hits the
view in question...

Code:

Set rst = CurrentDb.OpenRecordset("Select * from tblTableName",
dbOpenSnapshot) 'tblTableName is a list of all tables/views to
connect too

Do Until rst.EOF
strdboName = "dbo_" & rst!TableName
On Error Resume Next 'ignore error if the table doesn't exist to
delete
CurrentDb.TableDefs.Delete (strdboName)
On Error GoTo ErrHandler
strodbc = "ODBC;Driver={SQL Server};Server=ZZZ;Database=AAA;WSID="
& Environ("UserName") & ";uid=XXX;pwd=YYY"
DoCmd.TransferDatabase acLink, "ODBC Database", strodbc, acTable,
rst!TableName, strdboName, , True
rst.MoveNext
Loop


The DB is converted to an MDE and stored on the server. A shortcut
points to a .BAT file to download the MDE to the user's HD and execute
it. The above code is called from the slash screen to update the
links.

Per the setup the DBA has given me, everybody that executes the MDE is
logging in using a common UID and PWD (that none of the users know).

So while this is obviously not a highly secure set up, its the hand I
have been dealt. My goal is simply not to have the PWD where it is
easily found. Given the user population, anything that requires code
to access is not easily found.

 

[Tom]

Any chance of switching to Trusted Connection? No SQL Server IDs to manage
then...

Doug

If I understand trusted connections correctly (and I'm a SQLSERVER
novice, so I might not), while I could use a trusted connection, I
would have to have the DBA make sure all the users were mapped to the
right UID with the proper permissions in the DB. Unfortunately, there
is virtually no chance that I would be successful in getting the DBA
to change the way he does things.



Tom

 

[Douglas J. Steele]

It's been a while since I've done any SQL Server DBA-type work, but that's
not my recollection of how it needs to work.

You can grant permissions to global groups, and just make sure that all
users are in the appropriate groups.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Any chance of switching to Trusted Connection? No SQL Server IDs to manage
then...

Doug

If I understand trusted connections correctly (and I'm a SQLSERVER
novice, so I might not), while I could use a trusted connection, I
would have to have the DBA make sure all the users were mapped to the
right UID with the proper permissions in the DB. Unfortunately, there
is virtually no chance that I would be successful in getting the DBA
to change the way he does things.



Tom

 

[Tom]

It's been a while since I've done any SQL Server DBA-type work, but that's
not my recollection of how it needs to work.

You can grant permissions to global groups, and just make sure that all
users are in the appropriate groups.

Doug:
Hmm - I'll go back to the DBA so he can educate me so more, but I
think the crux of the problem is making sure all users are in the
appropriate group. He doesn't want to deal with that issue. He's
issued me a single UID and PWD that have read/write permissions to the
DB and expects all my users to make use of them.

Perhaps I should just change to a DSN connection and move on?

Thanks

Tom

 

[Douglas J. Steele]

Just to be clear, the DBA wouldn't be managing the group membership. These
are groups in Active Directory, not something in SQL Server.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It's been a while since I've done any SQL Server DBA-type work, but that's
not my recollection of how it needs to work.

You can grant permissions to global groups, and just make sure that all
users are in the appropriate groups.

Doug:
Hmm - I'll go back to the DBA so he can educate me so more, but I
think the crux of the problem is making sure all users are in the
appropriate group. He doesn't want to deal with that issue. He's
issued me a single UID and PWD that have read/write permissions to the
DB and expects all my users to make use of them.

Perhaps I should just change to a DSN connection and move on?

Thanks

Tom

 

[Armen Stein]

Just to be clear, the DBA wouldn't be managing the group membership. These
are groups in Active Directory, not something in SQL Server.

Yes, that's right. The DBA just needs to assign permissions to the
Group once. Then a network admin can manage who's in that group.
Still takes more effort than a single SQL login, but it's not too bad.

Couple more things to consider:

- if you use Windows authentication, those users will have the same
permissions to the database from elsewhere too. Like Management
Studio or Excel or Access. Hopefully they don't go in and edit/delete
records that way.

- Windows authentication requires all users to be inside your domain
(sounds like they are, but I just wanted to point it out for others).

- Windows authentication allows triggers in SQL Server to more easily
record the user that makes changes into an audit trail. Using SQL
authentication requires you to place the Windows login into each table
for the trigger to pick up. Doable but not as slick.

- There are ways to make it harder (but not impossible) to see the
credentials in your Access app. I have a slide on this in my Best of
Both Worlds deck at www.JStreetTech.com/downloads. It also includes
some thoughts on when to use SQL Server, performance considerations,
concurrency approaches, and techniques to help everything run
smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tom]

Armen & Doug:

I just got done listening to the DBA. He's not budging - I get one
UID and PWD for all users to access this DB. (I make him out to be a
bear - he's actually a nice guy - this is just the way he wants it
done and its his game - so I play by his rules).

My intent was to make this an MDE, hide the DB window, but not
necessarily disable bypass keys. So it sounds like my options are one
of the following:
* figure out why I can't index the view
* move to a DSN connection
* live with having the password in the properties of the pass-through
query

Other ideas are welcome...

Thanks

Tom

 

[Stefan Hoffmann]

hi Tom,

I'm using the code below to relink and I see it every time it hits the
view in question...

You should have posted this the first time...

Code:
DoCmd.TransferDatabase acLink, "ODBC Database", strodbc, acTable,
rst!TableName, strdboName, , True

This is the problems origin. You should use the TableDefs collection
instead:


Public Function TableLinkODBC(ASourceName As String, _
Optional ADestinationName As String = "", _
Optional APrimaryKey As String = "") _
As Boolean

On Local Error GoTo LocalError

Dim CONNECTION_ODBC As String

TableLinkODBC = False

' You must build your connection string somewhere...
CONNECTION_ODBC = "ODBC;" & _
"DRIVER={SQL Server};" & _
"SERVER=(local)\SQLEXPRESS;" & _
"DATABASE=Doh;"

ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
End If

If TableExists(ADestinationName) Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete ADestinationName
End If

Debug.Print "+"; ASourceName; "="; ADestinationName
CurrentDbC.TableDefs.Append _
CurrentDbC.CreateTableDef( _
ADestinationName, 0, ASourceName, CONNECTION_ODBC)
CurrentDbC.TableDefs.Refresh

If APrimaryKey <> "" Then
SQLExecute "CREATE INDEX pk_" & ADestinationName & " ON " & _
ADestinationName & "(" & APrimaryKey & ") WITH PRIMARY;"
End If

TableLinkODBC = True
Exit Function

LocalError:
MsgBox "Do'H!"

End Function

mfG
--> stefan <--

 

[Douglas J. Steele]

How are you trying to index the view?

Once you've got the linked table created, see whether you can run a DDL
statement like

CREATE INDEX __UniqueIndex ON [NameOfTable] (Field1, Field2, Field3)

 

[Tom]

Awesome - Thanks for the better method. It seems to work great, even
with the stubborn View

Tom

 

[Tom]

In SQL Server Management Studio, right clicking on the index "folder"
for the view and selecting new view. Then setting it up with the
three primary keys that I am grouping by in the view. I've included
the "COUNT_BIG(*) AS Count" as required, its not based on any other
views an it seems to meet all the other requirements.

In fact in an earlier design it worked fine. I had to revise my table
design though and that is when things broke.

If you're interested, I can post both the original and revised table
design and query. However, basedon Stefan's second post, I seem to
have things working.

Thanks to both of you for your assistance!