[MS_AccessPros] Re: upgrade to sqlserver, help needed

 

Crystal - you have to use dbSeeChanges for any recordset using SQL tables that have IDENTITY columns (AutoNumber). And you have to refresh links to SQL tables if you make a design change. What I do is have a local table with a datetime field that is checked against a back end table. When I make a design change in a SQL table I update the back end. Using the startup form's Activate event, I compare and force a relink if necessary.

    'Check if tables have changed since last Relink.

    'SQL links do not refresh automatically.

    If Nz(DMax("DateRelinked", "tblRelink"), 0) _

            < DMax("DateDesignChange", "DesignChange") Then

        Call RelinkSQLTablesDAO(True, False, Me.Name)

    End If

Taking it one more step, if you make a change in a SQL table that is a member of a view, you hve to refresh the view within SQL and then force a relink in the Access front end.

All of the above should indicate to you how important it is to plan out your database design well in advance so you are not constantly making design changes during development.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@yahoo.com> wrote :

Hi Bill,

1. Is it ok to use dbSeeChanges when setting rs for tables that are in Access? I may not know where they are ...

2. If a column changes in SS, will the table relink when Access is opened again?

thanks for the article, Bill

respectfully,
crystal

~ have an awesome day ~

On 11/11/2016 10:12 AM, wrmosca@comcast.net [MS_Access_Professionals] wrote:

Onno

Most of my Access applications use linked SQL tables. You can treat them just like Access tables. You don't need to use pass-through queries in most cases. Just regular Access queries. That lets you use all your functions as you normally would. Having an Access front end gives you the power of VBA.

There are a few things to understand about using SQL tables. I've written an article concerning this topic. http://www.thatlldoit.com/Pages/howtosarticles.aspx

 

 Look for the article titled "Working with SQL Server Linked Tables: The Hidden Gotchas"

Sorry about the anchor links not working...Another "MS broke my site" issue.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <onno.knol@pbl.nl> wrote :

Dear pro's,

My mscaccess database has grown  so much that I need to upgrade to SQL server.

I am in need of some guidelines,  is there a or tutorial or a good book?

 I already followed the upgrading wizzard, but I have more questions, like:

Can I use VBA in the sql server  environment? or is there another scripting facility?

How do I write passthru queries with built in functions ?

I seem to understand that  that is not transferable from msaccesss, what is the  solution in sqlserver environment?

Hope you can give me some good advise!

Thanks in Advance

Onno Knol

 

__._,_.___

---

Posted by: wrmosca@comcast.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___