Access upsizing problem

[xfile]

Hi:

I have an annoying problems with Access (2003) upsizing to a remote SQL
server.

According to MS KB article 838594, I shall use DSN but that was exactly what
I have done.

Error messages persist:
------------------------
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access
denied.

The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5
or higher). Please log in to a SQL Server data source.
--------------------------

I am sure that SQL server is not down since I am using Enterprise Manger to
access it at the same time. I am sure SQL version is 2000.

Contrary to what the article suggested, I could however pass through with
"create a new database" instead of "use existing database". However, that
will just skip the error message and no actual database have been created.

By the way, what will be the best practice for working with Access and MS
SQL to maintain design and records as much as possible?

I just learned that there are a few ways of doing it, such as upsizing,
using Import data from SQL, creating a data access page from Access or using
Access project, which I never could edit data from it.


Any help is deeply appreciated.

 

[Norman Yuan]

Some comments inline.

Hi:

I have an annoying problems with Access (2003) upsizing to a remote SQL
server.

How remote? inor outside your network domain/LAN?

According to MS KB article 838594, I shall use DSN but that was exactly what
I have done.

Error messages persist:
------------------------
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access
denied.

The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5
or higher). Please log in to a SQL Server data source.

Sounds like login problem. What login type you use (Windows loggin, or SQL
loggin)?

I am sure that SQL server is not down since I am using Enterprise Manger to
access it at the same time. I am sure SQL version is 2000.

Contrary to what the article suggested, I could however pass through with
"create a new database" instead of "use existing database". However, that
will just skip the error message and no actual database have been created.

By the way, what will be the best practice for working with Access and MS
SQL to maintain design and records as much as possible?

By "maintain design and records", if you mean you use Access for developing
and SQL Server is the target production environment, I recommend you to use
MSDE in your development. Since MSDE IS SQL Server, you can easily transfer
your database design to SQL Server without any change.

 

[xfile]

Hi:

Thanks for your kind reply.

To answer your questions:

(1) The target server resides at hosting service, and yes, it is outside the
network domain and using Internet to access it.

(2) I am sure that it is not a login problem. I was using SQL login with
the same information as using Enterprise or DSN. I could verify the
connection is good when I added the DSN to the Windows ODBC. And according
to MS article, that might be a problem for upsizing when Access and SQL are
not at the same computer (which is my situation) except the solution does
not work.

(3) Yes, you're correct about that it is my purpose (if possible and still
under testing) to use Access as the initial development environment for its
convenience and availability of templates from MS, and the fact that we are
not familiar with SQL at all.

The production environment will be SQL and we will migrate to it gradually.

I am sure that I have MSDE installed as well as SQL Express 2005 beta 2, but
how do we start using it? Are there any client tools to use it?

Thanks again.

Some comments inline.

Hi:

I have an annoying problems with Access (2003) upsizing to a remote SQL
server.

How remote? inor outside your network domain/LAN?

According to MS KB article 838594, I shall use DSN but that was exactly what
I have done.

Error messages persist:
------------------------
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access
denied.

The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5
or higher). Please log in to a SQL Server data source.

Sounds like login problem. What login type you use (Windows loggin, or SQL
loggin)?

I am sure that SQL server is not down since I am using Enterprise Manger to
access it at the same time. I am sure SQL version is 2000.

Contrary to what the article suggested, I could however pass through with
"create a new database" instead of "use existing database". However,
that
will just skip the error message and no actual database have been
created.

By the way, what will be the best practice for working with Access and MS
SQL to maintain design and records as much as possible?

By "maintain design and records", if you mean you use Access for
developing
and SQL Server is the target production environment, I recommend you to
use
MSDE in your development. Since MSDE IS SQL Server, you can easily
transfer
your database design to SQL Server without any change.

I just learned that there are a few ways of doing it, such as upsizing,
using Import data from SQL, creating a data access page from Access or using
Access project, which I never could edit data from it.


Any help is deeply appreciated.