XML transfer of ACCESS table to MSDE2000 within VS 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hugh O - 17 Aug 2005 20:17 GMT

I have a MS Access 2000 application that I would like to implement as an
Internet application via Visual Studio 2003 using Visual Basic.Net. I am
planning on a total rewrite of the code to take advantage of the Object
Oriented features of VB.Net.

However it would save considerable work effort if I could transfer existing
tables within my MS Access application and import them into the MSDE2000 SQL
database that I am creating for my Internet application. There is an easy
export facility that creates an XML file within MS Access. Is there a way
of importing that XML file into my MSDE2000 environment so I can access and
modify that imported table via the Server Explorer component of Visual
Studio 2003?

If this is possible, where or how can I learn the process steps?

This could save me a considerable amount of work, if it eliminates the need
to recreate that data from scratch.

thanks,
Hugh

Reply to this Message

Kevin Yu [MSFT] - 18 Aug 2005 09:15 GMT

Hi Hugh,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to transfer data from Access
2000 to MSDE database. If there is any misunderstanding, please feel free
to let me know.

As far as I know, MSDE doesn't support SqlXML. So we cannot get Xml data to
MSDE directly. It would require us to write addition code to do the data
trasfer, that is to read table data from Access, save it to the DataSet and
then insert them to the MSDE table.

So in this case, I suggest you use the Data Transformation Service (DTS).
You can create a DTS package on a machine that has SQL Client tools and run
it on your machine. Also, you can transfer data directly using the DTS tool
from Access to MSDE instance. For more information of DTS, please check the
following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_
basic_5zg3.asp

HTH.

Kevin Yu

Signature

=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this Message

Hugh O - 18 Aug 2005 19:21 GMT

Kevin,
Thanks very much. This sounds exactly what I need. If I can implement it,
it will literally save me months of work recreating the data that I already
have, data that may only need some reformatting.

I have a MSDN Universal subscription and SQL Server 2000 and SQL Server 2000
Developer Edition is included. I have found out that the DTS can be
deployed into a MSDE environment but it is not supported by MSDE. I believe
that I may only need the Import / Export Wizard but I do not know if that
Wizard is considered deployment, probably not.

Would you know if I could install the SQL Server Developer Edition on my PC
and use it like I am currently using MSDE (MS SQL Desktop Engine). After I
installed Visual Studio.Net, I downloaded and installed MSDE because it
seemed to be a simple and sufficient database environment. If the SQL
Server Developer Edition would create the same or similar environment on my
PC (without the requirement to have a separate server), it might be that my
answer is to simply install SQL Server Developer Edition and use it instead
of MSDE and use then use the supported and included DTS to copy my MS Access
data.

Since this is getting into more SQL questions than VS.Net, do you know a
good Newsgroup for me to ask these questions?

Thanks a great deal you have helped a great deal and have given me real
hope. This seems so close and possible that I must follow up on it. DTS
sounds like it will save me months of work effort recreating existing data.

hugh

> Hi Hugh,
>
[quoted text clipped - 28 lines]
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."

Reply to this Message

Kevin Yu [MSFT] - 19 Aug 2005 09:27 GMT

Hi Hugh,

Since you have SQL Server 2000 Developer Edition, I strongly use it instead
of the MSDE. The MSDE is the SQL Server desktop engine. It is using the
same engine as SQL Server, but has a lot of limitations. However, it is
free.

Actually MSDE is a SQL server instance, which you can connect to using the
SQL Server client tools. (Like Enterprise Manager and Query Analyzer. These
tools are not available in MSDE installation. You need to get a SQL server
) If you already have SQL Server 2000 developer edition, please install it
as the database. You can use the DTS wizard directly to import and export
data from Access to your new database.

You can also post in the following newsgroups for specific SQL server
questions.

microsoft.public.sqlserver.programming
microsoft.public.sqlserver.tools

Kevin Yu

Signature

=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this Message

Hugh O - 19 Aug 2005 23:18 GMT

Kevin,

Just wanted to say thanks.

I have significant amount of mainframe software experience in my career. I
have been working slightly more than two years implementing this idea I had
to market a software program. I initially chose MS Access because it was
easy to use. I ended up acquiring a significant amount of Visual Basic
experience. Then I decided that the application might have a better chance
as a web application. I had no web experience and less MS server based
experience.

Slowly I have educated my self into understanding and using Object Oriented
coding of VB.Net. I have gotten this far avoiding any unnecessary
complexity and by keeping within the bare requirements to get my application
working. Now I guess I am going to learn SQL Server.

But I have to remark that this environment of Universal MSDN and all of its
support features has given me the feeling of never being alone. And the
education that I got was through its software features and newsgroup.

Just wanted to give you a little background and a sign of my appreciation
for people like you who have helped people like me. I think my application
holds real promise. But if it is ultimately successful, it will be from
major help like you have given me.

This will save me months of work that would have been required to recreate
that MS Access data.

thanks,
hugh

> Hi Hugh,
>
[quoted text clipped - 22 lines]
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."

Reply to this Message

Kevin Yu [MSFT] - 20 Aug 2005 04:32 GMT

You're welcome, Hugh.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu

Signature

=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this Message

Rate this thread:

XML transfer of ACCESS table to MSDE2000 within VS 2003

Free Magazines