Create an import .sql file

Question

Create an import .sql file

Antony Symonds

Ten Centuries

Points: 1324
More actions
April 13, 2006 at 9:53 am

#68208

Hi,
I am writing a new web app.
As a test I create a local iis server and wrote it to access data from a access MDB file. I know wish to move it to my web hosting companies servers ready for launch. The host server doesnt support MDB file access so I have to use SQL server. Which is what i intended anyway.
What my question is, is how do I create a file I can import onto my hosts web server to re create all the tables in my access MDB.
I dont use anything but the tables, the data im not too fussed about taking across but would be a bonus.
I have limited SQL server experience. But have used in the past .sql files that basiclly create the tables and fields for me. Is it possible to pull this out of access! The web hosting company does provide an import facility so I figured tis possible....just how do i create the sql file??
Thanks in advance
Antony
Time to make a change

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

a Lot of your options depend on what kind of "import facility" your ISP offers. You could for example if you have an ODBC datasource created to talk to your db, you could right click on your table in access and choose export, then choose ODBC databases() from the save as type drop down and follow the prompts.

Or if your ISP allows you access to your db via enterprise manager you could do the export from that side.

Also if you can manage your db from enterprise manager, I'd imagine that you'd be able to use the Access upsizing Wizard as well and just point it at your SQL server and specify the log in credentials and you'd be all set.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Dick Schroth Right there with Babe Points: 725 More actions · Answer 2

As Luke said, I would ask for some guidance from your ISP. Many do not allow direct access to the SQL Server, but I would think that if you had access to your own SQL Server you could use the upsizing wizard to create the database there, and then send a backup file to the ISP for them to restore.

Dick

Antony Symonds Ten Centuries Points: 1324 More actions · Answer 3

Hi,

Thanks for your reply....

Unfortunately I have no access to ODBC on the ISP server. And I cant upsize the access DB. The only option I have for import is a .sql file.

I dont have access to my own SQL server so I cant upsize as it requires one

THanks in advane

Antony

Time to make a change

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

believe it or not you can have access to a SQL Server. Go download the MSDE. Or a trial version of SQL Server 2000 so that you get enterprise manager. Install it, then you can upsize your access db and then have enterprise manager script out your tables, of course depending on how many or how few tables we're talking about it might just be easier to recreate them by hand. Or you could always type the script out by hand... Here's a reference you can use http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

To help us help you read this[/url]For better help with performance problems please read this[/url]

Antony Symonds Ten Centuries Points: 1324 More actions · Answer 5

Just to conclude this..in case anyone else has the problem i did the following.

I downloaded the trial version of SQL and installed it on my local computer. (as recommended above )

I then upsized my access DB to that server( all worked allthough had some problems with the upsize_ts field)

Then i found some software called MSSQLDump.exe which created a text .sql file which I could then put through the import function my ISp had provided and (although i REALLY hate this saying) badda bing I had my tables etc and data all stored on my ISP's SQL server woohoo \o/

Time to make a change