SQL Database access problem

Can't use SQL Server Enterprise Manager...

[giggle]

Hi

My Hosting company won't let me connect to our database using SQL Server Enterprise to upload data to the tables.

I've written VisualBasic code to create text files from the Access database, then FTP the textfiles up to the web site, and ASP code to read through the text files and write the data to the SQL tables and it works, but I was thinking that there must be an easier way to populate our database tables than doing this. (data changes quite often).

Does anyone know of a way to get data out of an Access database and send it up to an SQL database?

Anyone any ideas? Can't imagine that all the other companies that use this (very popular) UK based hosting company have to go through this.

Cheers

Mick

[IanTurner]

It seems very odd to offer SQL Server hosting and then not allow access via Enterprise Manager, I'd move hosts if I were you - most SQL Server hosting companies allow connectivity via Enterprise Manager.

[IanTurner]

Oops didn't really answer the question.

You may be able to access DTS from ASP to transfer the data into the SQL database or you can connect to the Access database directly from ASP and then load the data into SQL Server via ASP.

How did you get your SQL Server database set up if they don't allow you to use Enterprise Manager?

[giggle]

Hi Ian

It is a pain, I never thought to ask wether you could use Enterprise Manager before I signed up (the company is PIPEX - supposed to be one of the most popular in the UK).

They give you access to a browser method of maintaining your table (painfully slow). So I set up the tables then developed the VB and ASP stuff to populate the static tables.

I'll have to do a bit of research to find out what you mean about Access DTS from ASP...

Thanks

Mick

[txbakers]

I haven't found a sure fire method of transferring from Access to SQL, but here is something I do:

export the data from Access to a text file, then import it into SQL. It's still manual, and I'd like to find an automatic solution eventually.

[aleksl]

DTS is "it". It is "Data Transformation Services", a tool in SQL Server Enterprise Manager.

I would do a DTS package that extracts data from Access to your local SQL Server installation. Local DB should emulate your production DB. Then these are possible:

1) If you don't collect any info in production, just do displays - backup your local DB and do a DB restore on the hosting server. This will overwrite your prod. database every time.

2) Write a script that will read local DB tables and insert data into production tables. Since tables are exactly the same, you should be able to create one script for all.

2.1) You can even go fancy with it - build an "XML gateway" - a page on the server that accepts XML data. Then your script will extract table data as XML and send it to the gateway. Gateway then inserts data into the database.

Not to have Enterprise Manager access sucks