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

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.

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


"Hans" <(E-Mail Removed)> wrote in message
news:8218B57E-9B02-409B-B59E-(E-Mail Removed)...
> 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

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.

"Sylvain Lafontaine" wrote:

> 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.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Hans" <(E-Mail Removed)> wrote in message
> news:8218B57E-9B02-409B-B59E-(E-Mail Removed)...
> > 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
>
>
>

"Hans" <(E-Mail Removed)> wrote in message
news:BF35DB0C-2864-455A-81F5-(E-Mail Removed)...
> 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.

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.

"Norman Yuan" wrote:

>
> "Hans" <(E-Mail Removed)> wrote in message
> news:BF35DB0C-2864-455A-81F5-(E-Mail Removed)...
> > 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.
>
>

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/...efused-it.aspx

http://blogs.msdn.com/sqlexpress/arc...05/415084.aspx

http://blogs.msdn.com/sqlexpress/arc...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)


"Hans" <(E-Mail Removed)> wrote in message
newsDCDE559-6896-4D5F-82A2-(E-Mail Removed)...
> 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.
>
> "Norman Yuan" wrote:
>
>>
>> "Hans" <(E-Mail Removed)> wrote in message
>> news:BF35DB0C-2864-455A-81F5-(E-Mail Removed)...
>> > 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.
>>
>>

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


"Hans" <(E-Mail Removed)> wrote in message
news:8218B57E-9B02-409B-B59E-(E-Mail Removed)...
> 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