Access and SQL (1 Viewer)

[Villarreal68]

Hello everyone,

I have a dilema. I'm loosing a front end application that manipulates an SQL database. I'll be able to keep the SQL server and data, but I can't keep the front end app.

What do you recomend I do. Should I export all the data from the SQL server and create an MS Access database to house it in, or is it possible to create a front end Access database to manipulate the SQL server database?

I'm starting my journey into a resolution and I thought I could get some pointers from a knowledable source.

Appreciate any and all help on this dilema.

Thanks in advance for your comments.

rené

 

[Moniker]

I would be far easier to keep the data in SQL Server as the backend and write a simple frontend in Access that accesses that data. SQL Server and Access, both being Microsoft products, work very well together. At work, I hit DB2, SQL Server, and a few others regularly through Access. Also keep in mind that SQL Server will handle larger volumes of data more efficiently than Access, and SQL Server doesn't have the 2GB limit.

 

[Villarreal68]

Hey Moniker,

Thanks for your quick reply. So how hard is it to create an Access FE db to access SQL? WOuld it be the same as creating a FE and BE Access db? Any help would be greatly appreciated.

René

 

[rhett7660]

I too am interested in doing this. Can you use access to get to the backend directly? Or would need to create some other "bridge" to the sql backend?

Thanks
R~

 

[pbaldy]

If you link the tables via ODBC, it will basically be like working with an Access BE.

 

[Moniker]

What Baldy said.

Essentially, you use Access to link to your BE database, and you can manipulate it in Access just like you were using SQL Server. Is it easy? It depends on what you're doing. If you're just doing basic stuff (selects, appends, updates, deletes), then it's basically the same as having everything local.

I'm sort of out of time here, and I have no idea what your expertise level is. Depending on your ability to write SQL and manipulate data on the fly, it's either relatively easy or perhaps out of your league. None of it is particularly difficult given the right experience level, but if you're brand new to this concept, it can seem overwhelming.

The best advice is to try it and get as far as you can on your own, and then ask relevant, specific questions that get answered far more than the "write this for me!" questions.

 

[rhett7660]

Moniker..

Will do, I am very new to the sql server,,,,,,, but that is the direction I would like to go.

Thanks again
R~

 

[Villarreal68]

Thanks Pbaldy and Moniker.

You guys are always available and "Access"ible. Thanks! I will try it! I'll let you guys know how it goes.

rené

 

[Villarreal68]

I have so far been able to link the tables on the SQL server to a FE Access database. I have one question:

How can I achieve the "Relationship" status that the SQL has on the Access db? When a link is created between an Access FE and BE the "Relationships" are kept, but I noticed that when I linked the Access FE to the SQL db, the relationships were not there.

How can I retrieve the "Relationship" list from the SQL db?

I hope I explained the issue clearly enough if more detail is needed let me know. thanks!
Any help is greatly appreciated.

René

 

[Moniker]

In Access, press F11 to get to the database window. In the top row of icons on the toolbar toward the right, there is a "Relationships" button that looks like three forms connected by lines. I'm not sure if that will show you the relationships as they exist in SQL, but I think it's pointing you in the right direction.

Also in Access, there's a built-in documenter that shows any or all the relationships you want (Tools -> Analyze -> Documenter). Perhaps there's an equivalent in SQL Server? (I haven't used that in about six years.)

 

[Villarreal68]

Hello Moniker,

Thanks for the response. I was aware of the Relationships butto and the documenter, what I'm trying to get to is how do I find out what are the Relationships in SQL. Is there a tool or management console I need to run? Or do I even have to worry about that? Maybe I don't need to enter Relationships and the SQL server will take care of all that (wishful thinking)...

Thanks again!
René