SQL Server Database with Access Front End

[rumblecat]

I have "upsized" an Access back end database to SQL Server. The database contained some fields which were type Hyperlink and Autonumber in Access, and their data types were re-assigned during the SDL Server translation. We need to continue using the Access front end to enter data into the database for the near term. How do I modify the Access front end so that I can both enter Hyperlink data paths and resolve links to the converted files in the SQL Server database?

 

[bparkinson]

I have "upsized" an Access back end database to SQL Server. The database contained some fields which were type Hyperlink and Autonumber in Access, and their data types were re-assigned during the SDL Server translation. We need to continue using the Access front end to enter data into the database for the near term. How do I modify the Access front end so that I can both enter Hyperlink data paths and resolve links to the converted files in the SQL Server database?

In SQL Server, the equivalent to an AUTONUMBER column is an Integer column with it's "Is Identity" property set to Yes. There is no hyperlink type in SQL Server. You could, however, store the URL as text in a column in your SQL Server table, and then assign it to the Hyperlink Address property at form load time, for example.

 

[rumblecat]

There is no hyperlink type in SQL Server. You could, however, store the URL as text in a column in your SQL Server table, and then assign it to the Hyperlink Address property at form load time, for example.

Thanks for reply, will look at the autonumber issue shortly.

My problem is that I am entering the URL information via the Access front end form(s) into the SQL Server table. Prior to the conversion to SQL Server we had a clean display within the Access form "LinkName" for the actual underlying URL "\\server\group\Released Documents\LinkName.ZIP", all of which was entered via the form as data type HYPERLINK.

Conversion to SQL Server using SSMA leaves us with
"LinkName#\\server\group\Released Documents\LinkName.ZIP#" as data type MEMO.

Any ideas? It's painfully obvious that using Access as a front end to SQL Server is far from ideal, but certainly I can't be the only one who has ever "upsized" and encountered these issues.

 

[boblarson]

It's painfully obvious that using Access as a front end to SQL Server is far from ideal,

Actually Access as a frontend to SQL Server is a great tool. The problem lies with the developer of the Access application in the first place. If they ever expect the database to be upsized to SQL Server then several things must be done to ensure that everything will be compatible. If they never thought that it would happen, then it could be a painful transition because you have to go fix the stuff that should never have been designed like it was due to SQL Server and Access being different beasts. The same would be if you used Oracle or MySQL. Each has its own unique things about it and you need to know what you are doing is going to be compatible.

So, if you design an Access frontend to a SQL Server backend that has never been in Access, you would immediately be forced to use certain design principles which would be just fine and it would work fantastic.

 

[rumblecat]

Bob,

Point taken. I shouldn't expect applications developed in any system to be entirely portable. And no, the Access front end and original back end database were never developed with SQL Server in mind.

My frustration stems from not being able to come up with a workable solution to my dilemma. My option at this point appears to be to completely redo the front end - that being the case, Access isn't a good choice.

 

[boblarson]

My option at this point appears to be to completely redo the front end - that being the case, Access isn't a good choice.

Well, the thing to note, is even if you have to redo the frontend in Access, the development time is likely going to be at least 10 times shorter than if you try to do it in something like .NET. And since it would be that much shorter development time, then it would also cost 10 times less to redo it. So, just be aware of that.

As for the frontend, you probably don't need to redo the whole thing. But you will need to change the way you deal with links. You could create a new field in SQL Server to store the display value of the link and then use an append query to append the display part to that table and then an update query to strip that part from the current field. You can then modify the frontend to store the parts where they need to be but you can display the links by using some code to make it look like they would have normally looked. You can set the IsHyperlink property of a text box on the form so the hand will appear just as if it was a link and you can set the color and underline property so it looks like a link. The users wouldn't necessarily need to know that there was anything different.

 

[bparkinson]

Actually, as a RAD tool against SQL Server, Access is excellent. I usually can get a form built in minutes. If you take advantage of SQL Server views, stored procedures, and triggers, lots of functionality can be moved from the client to the server where it belongs.

That said, the learning curve for SQL Server can be steep. Coming from SQL Server as a DBA to using Access has been a learning experience for me, but I have a lot of VB6 experience from years ago that helped a lot. Once I got the hang of Access, it's been the fastest development work I've ever done.

 

[bparkinson]

What Bob Larson said about development time is right according to my experience. As to using update queries to deal with the hyperlink issue, that's OK too, but if you are on SQL Server you should instead use stored procedures instead of Access queries. Much more efficient, and they can execute asynchronously from the front end. Also, since you can call a stored procedure from a trigger, you can have it fire automatically in the backend afrter an insert or an update. Very convenient.

 

[Lightwave]

My problem is that I am entering the URL information via the Access front end form(s) into the SQL Server table. Prior to the conversion to SQL Server we had a clean display within the Access form "LinkName" for the actual underlying URL "\\server\group\Released Documents\LinkName.ZIP", all of which was entered via the form as data type HYPERLINK.

Any ideas?

Yes

Hyperlink is a really nasty data type and I'd recommend not using it. Memo or text is absolutely fine.

Firstly you may need to clean up the link as you see appropriate then simply place a button on the form next to the field containing the text of the link and in the on click event have something like the following

    Dim Link As String
    Link = Me.FieldNameonFormwhichhaslink
    Application.FollowHyperlink "http://" & Link & "/"

You may or may not need the prefix http and suffix / depending on how you have been typing in the information... The important thing is to get the cleaned field into a standard format so that you can get a hyperlink text out of it consistently.

But shouldn't be very difficult.

The good thing about this is that people can go in and edit the text feel that represents the hyperlink without automatically being fired to the link.

So I keep web addresses for companies simple field memo or string data type

-www.companyweblink.com

and then fire the Application.FollowHyperlink command and wrap http and / round it and hey presto.

I note that you look like you may be using hyperlink to access a file on your own server. This is slightly more complicated. I use a couple of functions which I got from here called bimshellOpenfile and shellexecute

I place them in a module and then call them from a button when needed.