MDF and SQL Server 7.0 Correlation

[sapbucket]

Hello,

I am a newbie. Need help with SQL Server. Right now I want to add a databaseconnection to the SQL Server. Currently, the only connections that show up are the connections that came with installation (like pubs and northwind dbs). I want to add a new connection based upon a microsoft access dB that I created. Is there some type of connection Manager? Anyway, here is an example to simplify my question:

I have a Microsoft Access dB that I just created. I now want to allow SQL Server to connect to it so that I can use the dB in my code. However, Access saves my new dB as a Access file in some directory, not as a .mdf file that VB.Net and SQL Server can recognize. The server doesnt know that it exists: how do I get server to establish the connection?

I tried playing around with the "Export" function within Access. Didnt help much.

Doesnt it seem like this should be more straightforward? I am sure that this is a very common problem for newbies to solve.

Thanks for the help

 

[olichap]

You can try importing the tables from your Access MDB into a new SQL Server database or just go ahead and create the database on SQL Server directly. Sounds like you want to "link" from SQL to Access the same way Access can to SQL; don't think it's possible but could be wrong.

 

[krisbrixon]

There are a couple of things going on here.

Without knowing all the details of your situation I would agree with olichap to move all your data to one database format. As long as you are using something newer than Access 2.0 then SQL Server will import the database into SQL Server (but then you have to use the SQL Server version and not the Access version).

Remember Access is not only a database management system, but also an application development system. While, SQL Server is not an application development system so it should not have some of the same functions as Access. If you have to use some information from Access and some from SQL Server then I think you would have to connect to each DBMS from your program and deal with the information in your program. Also, SQL Server does not "connect" to anything, it sees what it has. In your application you connect to things.

I also do not think it is common for "newbies" to be trying to use two DBMS's at the same time. That is usually for programmers trying to integrate (or convert between) two different systems.

I hope this helps

Kris

 

[sapbucket]

OK,

Thanks for help guys. I realized I was having a conceptualization problem. I thought that SQL Server was just a management tool - not a full blown relational database tool - and that to use it you first created your tables in some ODBC complient format and then "migrated" or "exported" them to .mdf format.

So, to move this question to the next phase, I would rephrase my origninal post to say this:

How do I use the data wizards (that is, not write any code directly into the .asmx file) to:

1.) use the OleDbDataAdapter Configuration Wizard within a ASP.NET project, using VS.NET Pro as my IDE, to setup the adapter for a ACCESS DB. When I try, using LOCALHOST as my server on Windows XP Pro, I get this error:

Error: 5172
"...FILE SIZE property is incorrect."

2.) connect to a .mdb (Access db) file?

Do I have to register the db with IIS 5.0 to make a system data source? (I have done this...)


Basically, I cant get the OleDbDataAdapter Configuration Wizard to open anything but SQL Server (.mdf) files. I want to open other databases! Thanks for the help,

Tom

 

[sapbucket]

Well,

I feel like an idiot. The solution to problem is thus:

When the OleDbData Adapter Wizard starts it automatically sets the Provider to SQL Server. There is a tab on the top of the form (after hitting "new connection&quot that allows you to change it to "Microsoft Jet 4.0 Ole Db Provider," and, when you click back to the Connection Tab you will have the wonderous screen that I have wanted for the past week: The Connection to my Access Db plain and clear.

Tom