Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-12 : 23:02:08
|
| Paul Kettley writes "I have created offline a large Access database, but when reading your articles about user productivity of using a access database.I decided to buy SQLServer and try to convert my access database to sql server database, but I am using a shared server by another company, which they do support sqlserver.But i cannot convert my database to SQLServer, because it wants a server to send it too.How can i convert my access database to SQLServer, on a home PC without a server?Yours Sincerely,Paul Kettley" |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-12 : 23:52:05
|
As far as I'm aware, you can't. At some stage you're going to need direct access (ie control of) a server running the version of SQL server you want to connect to.One way to go might be to convert your .mdb into an .mdf using MSDE - and Access 2000/2002. You'll need to create a new access project with new database, and then import your datbase objects. But even when you've done this, you'll still need to be able to "attach" your MSDE database to the SQL server. (although your nice host may agree to do only that much)Which version of Access do you have? Oh, and I didn't understand this linequote:
I decided to buy SQLServer and try to convert my access database to sql server database, but I am using a shared server by another company, which they do support sqlserver.
You bought it but they don't support it - or you didn't buy it, but they allow you to use theirs?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
Cassius
Starting Member
2 Posts |
Posted - 2002-03-13 : 13:48:26
|
| My host supports SQL Server 2000, and i also brought SQLServer 2000, so i can update it. but i had problems trying too, because i don't use a server just a home PC.I thought about just using SQLServer for myself, and the product i am building to also support Access and SQLServer.I just have to write the ASP script to build the database.Dam stupid microsoft should have a way of converting Access to SQLServer easily, not make it hard, so you have to do it all over again.Paul Kettley |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-13 : 14:47:15
|
| They do make it easy, there is a wizard to Upsize an Access database to SQL Server. This is built-in for Access 2000 (it's on the Tools menu under Database Utilities). You need to configure an ODBC data source that connects to the SQL Server, and that will also require the IP address of your host's SQL Server (they should have provided that to you).Once you set up the ODBC DSN, the wizard will ask which tables you want to transfer, whether you need to preserve foreign keys, cascades, indexes, and if you want to transfer the data as well as the structures. It may take some time to transfer the data to a remote host. It also gives you the option of linking the SQL Server tables into your Access database (it will preserve the Access tables too, so you won't lose any data).If you have Access 97 this wizard can be downloaded from [url]http://www.microsoft.com[/url], but you'll have to search for it (they've changed the location half a dozen times). Search for "Access Upsize Wizard". |
|
|
|
Cassius
Starting Member
2 Posts |
Posted - 2002-03-13 : 16:48:43
|
| How do you configure a ODBC data source to connect to a SQL Server?You went way over my head then!I only know SQL in Access, and a bit of SQL Server, but i know of Upsizing wizard, but i didn't have a clue about it.I will look into it, any info on how to use the wizard?Paul |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-13 : 17:08:39
|
| It's pretty easy to set up a DSN. Open up Control Panel and look for an option that says "Data Sources" or "ODBC Data Sources". When you open it, you'll see a tabbed control panel (ODBC Administrator) listing User DSNs, System DSNs, and File DSNs, as well as Drivers, Tracing, and Connection Pooling.If you choose the System DSN tab, you'll see some buttons that allow you to Add, Edit, or Remove a DSN (Data Source Name) from the list. Click Add.You'll be prompted with a list of the available data drivers. Near the bottom is the driver for SQL Server. Select it and hit Next (or Finish, depending on the version you have).The next screens will guide you through the settings. You'll need to enter a name for your DSN (this will appear in the ODBC window once you're done). You can also enter a description for it. There will be a drop down box listing the available SQL Servers. This will probably be blank. If it is, you can just type in the IP address of the SQL Server. Click Next.The next step is the login credentials. Since you're connecting to a server over the Internet, you'll need to use SQL Server authentication. You'll need the user name and password that allows you to connect to the server. If you don't have one, contact your host and ask them to provide one.The next 2 screens you can pass through, the default settings should work fine. You should end up on a "review" screen that lists the settings for your DSN. There will be a "Test Data Source" button; click it and see if it can connect to the server. If you get an error, make a note of it, and contact your host. They should be able to help you resolve the error.Once you have the DSN created, you can close out of the ODBC Administrator. When you run the Upsize Wizard, it will list the DSNs on your computer; choose the one you created to connect to SQL Server. Follow the rest of the wizard instructions, it's pretty fool-proof and really easy to use.If you practice creating DSNs (create them then remove them, try changing some settings) you'll get the hang of it in no time. |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-13 : 23:32:47
|
Another advantage to knowing how to create and use DSNs is that if you want to pull data from Excel (say a report for your boss), you can do so using the DNS you create on his machine. Then anytime he wants an updated report with current data, he opens the Excel file and Refreshes the Data.Further, if you had a mailing list, you could generate for letters in Word and import the userlist using a DSN connection. Bottom line: learning how to utilize a DSN connection will allow you to do a lot of things with a lot of programs using data from a database. I currently have create a VBA macro in Power Point that creates 28 different Power Point slides with charts on then based on data in my SQL Server database. Saves a lot of time and everyone is amazed at how I can create that Power Point so quickly. It's my little secret! FYIJeremyEdited by - joldham on 03/13/2002 23:36:32 |
|
|
|
|
|
|
|
|
Access to SQL Server Problem