We help IT Professionals succeed at work.

Advice on Access data connection to Sequel Server

Medium Priority
50 Views
Last Modified: 2020-03-23
In the light of no progress from MS about access windows 10 O365  bug I'm attempting a sequel server option.

Pretty weird how the simple updater now renames every key field, (is this normal?) meaning I have to go into 60 tables and rename the linking field so that any reference any of them in a form still works.

Just wondered, as my 30,000 lines of code are all access VBA, is it going to take forever to make something work?

Am I being pessimistic or realistic?

Any tips?
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
At first what " windows 10 O365 bug"
2nd SQL (sequel) server it can be as Access or it can be a whole new reality.
If you use the Linked Table scenario you will find that little changes are needed to make it work (probably correct Pk and timestamp column on SQL side)...but this might involve a cost on performance...might low ..might BIG

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<At first what " windows 10 O365 bug" >>

 he's referring to the leasing bug, which Microsoft still has not fixed.

<<Pretty weird how the simple updater now renames every key field, (is this normal?) meaning I have to go into 60 tables and rename the linking field so that any reference any of them in a form still works. >>

 Not sure what you are referring to here, unless you mean the dbo. prefix.  That's easy enough to strip off with a little code.

 If you are referring to the fact that your asked to identify a PK field when you link, it means you didn't put PK fields on the SQL tables before you linked.

 Really, hooking up to SQL doesn't require a lot of effort right off.  There are a few minor issues, but it usually works right off with very little effort on your part.
 
 However, there is lot that SQL offers to improve the performance of your app, such as views, stored procedures, and triggers.  Access also offers pass-through queries. So there can be quite a bit of effort required to take advantage of everything SQL offers.

 But in general, just hooking up to SQL is very straight forward in most cases.

Jim.

Author

Commented:
Thanks John

Hi Jim

Is there any chance you have that code to strip out the dbo prefix. I'm paying a guy $250 a day to do it manually and we would have to do that with every conversion we take on.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
$250/day vs asking question  in EE......something is wrong here.... :)
Check here for the dbo.prefix : https://www.wiseowl.co.uk/blog/s285/linkviewsvba.htm

Author

Commented:
Thanks John.  Just need to get it done quick.

I have passed that on

Author

Commented:
Hi Jim

Do you know where i can get  that code to strip out the dbo prefix. Would be a great help
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
In the link i posted it has code that does that exactly...
I don't have a handy SQL installation but judging by the code it should do the job

Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
It turns out that i had a forgotten remote sql database...so i can confirm that the code in the link works just fine...just linked a table using the code and using my "dbo" free name.

Author

Commented:
My apologies John. I passed that on to my colleague without reading the entire article. Fortunately he is more methodical than me.

I will keep all informed of progress.

Author

Commented:
Thank you both!!