Access 2007 ADP (Runtime) and SQL Express 2005

[Hans]

Hi Everyone,

I have a problems which I'm struggling to resolve. Basically I have coverted
my Access 2000 database implemented for a client to Access 2007 (ADP) and SQL
2005 Express, also got hold of Access 2007 runtime and developer extensions.

I created a runtime setup of my access 2007 application with a login screen
to appear first, installed it on a second PC on my LAN. When I run the
application I cannot my login form to display, all I keep getting the message
'Access is unable to connect to '<database Name>' on the '<Server Name>'.
Although I can resolve this on my network, what worries me is when I'm ready
to role it out to the client.

If I get this message on my setup in the office, how do I rectify this once
its ready to be ported to the clients network (of which the setup will be
different). I don't want to install my copy of office 2007 (the customer does
not have this), do a manual fix linking it to SQL 2005 express on site,
create new runtime and install it on some 10 to 15 Pc, plus the SQL express
database on the client server. Is there any automated route to check change
in network from Access 2007 ADP, and popup a box to allow the user to change
connection to where their copy of SQL Express database is located.

Secondly, does anyone have any suggestions or views on what I need to be
aware of when creating Access 2007 runtime application, considering that this
is my first attempt.

Thanks to all in advance

 

[Sylvain Lafontaine]

I don't have Access 2007 or its runtime, so I cannot help you much on this;
however, one solution for you would be to use the Configuration Manager of
SQL-Server 2005 to create an Alias on your machine matching the address of
the server at your client site. This way, you won't have to change the
connection of the ADP project when copying it to your client's sites.

 

[Hans]

Thanks for that bit of information, as you know i'm on a steep learning curve
to educated my self on this.

Okay, managed to setup the alias, I was wondering if you knew the connection
syntax for connecting to SQL Exresss from within ADP using alias, I currently
use:-

dbConn = "Provider=SQLOLEDB.1" _
+ ";Server=OVSL-2\QiSSystem" _
+ ";Database=2iQ-QiS" _
+ ";User ID=" & Forms![Global]!username _
+ ";Password=" & Forms![Global]!Password _
+ ";Trusted_Connection=True" _
+ ";Integrated Security=SSPI;"

The above works fine, please advise if you can improve on the above.

 

[Norman Yuan]

Thanks for that bit of information, as you know i'm on a steep learning
curve
to educated my self on this.

Okay, managed to setup the alias, I was wondering if you knew the
connection
syntax for connecting to SQL Exresss from within ADP using alias, I
currently
use:-

dbConn = "Provider=SQLOLEDB.1" _
+ ";Server=OVSL-2\QiSSystem" _
+ ";Database=2iQ-QiS" _
+ ";User ID=" & Forms![Global]!username _
+ ";Password=" & Forms![Global]!Password _
+ ";Trusted_Connection=True" _
+ ";Integrated Security=SSPI;"

The above works fine, please advise if you can improve on the above.

No, the above DOES not work fine (even it may work on your computer fo r
some reason). The ConnectionString's security part simply does not make
sense: if you use Trusted_Connection (Integrated Security, they are the same
thing here), you never need to pass User ID/Password. Also, since you use
SQL Server Express, you can only use UserID/Password when SQL Server's mixed
security mode is enabled (it is not by default).

This indicates that more study on SQL Server security is needed before you
can deal with user logging into the database correctly.

 

[Hans]

Okay, point taken. As a learner i'm bound to make mistakes.

most of what I have learn't comes from information collected over the
internet.

I have removed trusted connection from the connection syntax.

Do you know of any resources on the internet where I can progress further
with ADP and SQL express 2005. I especially would like guidence on getting
SQL express on a network with the ADP clients connecting to it (LAN setup) .

My attempts at trying with two pc connected via a network hub seems to be
failing. I have a default installation of SQL express on one Pc, mixed mode.
The second PC cannot see the sql instance on first Pc.

Any pointer would be appreciated.

Thanks for that bit of information, as you know i'm on a steep learning
curve
to educated my self on this.

Okay, managed to setup the alias, I was wondering if you knew the
connection
syntax for connecting to SQL Exresss from within ADP using alias, I
currently
use:-

dbConn = "Provider=SQLOLEDB.1" _
+ ";Server=OVSL-2\QiSSystem" _
+ ";Database=2iQ-QiS" _
+ ";User ID=" & Forms![Global]!username _
+ ";Password=" & Forms![Global]!Password _
+ ";Trusted_Connection=True" _
+ ";Integrated Security=SSPI;"

The above works fine, please advise if you can improve on the above.

No, the above DOES not work fine (even it may work on your computer fo r
some reason). The ConnectionString's security part simply does not make
sense: if you use Trusted_Connection (Integrated Security, they are the same
thing here), you never need to pass User ID/Password. Also, since you use
SQL Server Express, you can only use UserID/Password when SQL Server's mixed
security mode is enabled (it is not by default).

This indicates that more study on SQL Server security is needed before you
can deal with user logging into the database correctly.

 

[Sylvain Lafontaine]

You should climb the ladder one step at a time. Before trying to set up an
ADP project using your own connection string, why don't you try the standard
Connection Dialog Window of ADP?

Also, the purpose of using an Alias is not to have anything to change -
connection or connection string - when you distribute your application to
your client's machines.

As to your connection problem between two pc, you can try anything else -
for example Sql-Server Manager Studio - to try to connect to a pc from
another pc. Once you able to connect with a program, connecting with any
other program should be easy. As you already knew, all Express are
installed by default in a restricted way (no external connection, no TCP/IP,
no unnamed instance or something like that). You don't say how this version
was installed nor if there were any previous installation of SQL-Server on
this machine or if other thing like the SQL Server Browser Service is up and
running, so it's hard to tell you more on this.

Try adding the prefix np: (for the Named Pipes protocol) before the name of
the server to see if this will help; you can also try the tcp: prefix. If
this doesn't work, you might have to add the name of the instance after it
or the port number (,1433 - for TCP/IP only) at the end; for example with
"sqlexpress" as the name of the instance:

sqlcmd -S tcp:NameOfTheServer\sqlexpress,1433

or with an unnamed instance and the named pipes protocol:

sqlcmd -S np:NameOfTheServer

You might have to deal with the Surface Area Configuration of SQL-Server
and/or its Network Client Utilities. For the 2008 version, I don't know
what these names are. By setting up an Alias, you can alleviate some of
these problems. Here are some more references for helping you on this topic
but I didn't check them recently. There are for SQL-2005 but most of this
stuff should be OK for the 2008 version:

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Okay, point taken. As a learner i'm bound to make mistakes.

most of what I have learn't comes from information collected over the
internet.

I have removed trusted connection from the connection syntax.

Do you know of any resources on the internet where I can progress further
with ADP and SQL express 2005. I especially would like guidence on getting
SQL express on a network with the ADP clients connecting to it (LAN setup)
.

My attempts at trying with two pc connected via a network hub seems to be
failing. I have a default installation of SQL express on one Pc, mixed
mode.
The second PC cannot see the sql instance on first Pc.

Any pointer would be appreciated.

Thanks for that bit of information, as you know i'm on a steep learning
curve
to educated my self on this.

Okay, managed to setup the alias, I was wondering if you knew the
connection
syntax for connecting to SQL Exresss from within ADP using alias, I
currently
use:-

dbConn = "Provider=SQLOLEDB.1" _
+ ";Server=OVSL-2\QiSSystem" _
+ ";Database=2iQ-QiS" _
+ ";User ID=" & Forms![Global]!username _
+ ";Password=" & Forms![Global]!Password _
+ ";Trusted_Connection=True" _
+ ";Integrated Security=SSPI;"

The above works fine, please advise if you can improve on the above.

No, the above DOES not work fine (even it may work on your computer fo r
some reason). The ConnectionString's security part simply does not make
sense: if you use Trusted_Connection (Integrated Security, they are the
same
thing here), you never need to pass User ID/Password. Also, since you use
SQL Server Express, you can only use UserID/Password when SQL Server's
mixed
security mode is enabled (it is not by default).

This indicates that more study on SQL Server security is needed before
you
can deal with user logging into the database correctly.

 

[bcap]

To stop Access trying to connect to a database as soon as the application
runs, issue the following statement in the Immediate window before you
create the ade or deploy the adp:

CurrentProject.OpenConnection ""