Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2018, 04:48 AM   #1
Kuhn
Newly Registered User
 
Join Date: Oct 2013
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Kuhn is on a distinguished road
access migration to SQL

Hello,

We're in the process of migrating our systems from access to another environment.

One of the first steps is to move our main tables that became too big to handle in access to an SQL environment, as a temp solution in transition to the new environment.

Performing simple queries on the migrated tables works like a charm, but once I attach them to a form that we use a lot, the database crashes on the first function (a simple 'search' button to find a number in the table). Is it because it uses the vba DAO commands, starting with Dim rst As DAO.Recordset?

Kuhn is offline   Reply With Quote
Old 08-10-2018, 04:52 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,086
Thanks: 38
Thanked 3,267 Times in 3,164 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: access migration to SQL

you are not giving us much to work with - it is unlikely to be vba DAO commands but always possible.

What does crash mean? access bombs out?, you get an error message? What is the sql that causes the problem?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-10-2018, 05:28 AM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,187
Thanks: 69
Thanked 1,394 Times in 1,315 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: access migration to SQL

Access will display a page of results from a linked table almost immediately. However if ordering is added to the RecordSource query or form properties it will have to wait until all results are returned before it can display anything.

This can look like Access has crashed.

Moving to a server backend is not a panacea for overcoming slow processes. It can exacerbate the problems.

Galaxiom is offline   Reply With Quote
Old 08-10-2018, 06:04 AM   #4
AccessBlaster
.
 
Join Date: May 2010
Posts: 822
Thanks: 19
Thanked 231 Times in 221 Posts
AccessBlaster will become famous soon enough AccessBlaster will become famous soon enough
Re: access migration to SQL

I would recommend watching this video by Steve Bishop. He speaks in plain English, and is easy to follow.

How To Migrate Access Tables To SQL Server Using SQL Server Migration Assistant
AccessBlaster is online now   Reply With Quote
The Following User Says Thank You to AccessBlaster For This Useful Post:
Kuhn (08-14-2018)
Old 08-12-2018, 11:09 PM   #5
Kuhn
Newly Registered User
 
Join Date: Oct 2013
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Kuhn is on a distinguished road
Re: access migration to SQL

thx guys. Will watch the video now.

the search function does use the (dim rst as DAO.recordset) rst.movefirst command and searching for a certain value.
I think I will first have to do some table cleaning as well (loads of unnecessary 255 char txt columns, and even long text). hopefully that will help a bit to start with.
Kuhn is offline   Reply With Quote
Old 08-13-2018, 02:16 AM   #6
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 103
Thanks: 18
Thanked 29 Times in 29 Posts
sonic8 is on a distinguished road
Re: access migration to SQL

Quote:
Originally Posted by Kuhn View Post
the search function does use the (dim rst as DAO.recordset) rst.movefirst command and searching for a certain value.
So, you are searching the Recordset for a certain value. - Bad idea in a client-server-environment. This forces all the data to be transferred to the client before your search can happen.

In a client-server-environment you should try to let the server do most of the work by using SQL queries to filter down the data first. Only after that you would do additional client side processing on a small sub-set of the data. (If still required at all.)
__________________
New Video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by sonic8; 08-14-2018 at 10:53 AM.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
Kuhn (08-14-2018)
Old 08-13-2018, 04:05 AM   #7
Kuhn
Newly Registered User
 
Join Date: Oct 2013
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Kuhn is on a distinguished road
Re: access migration to SQL

thx, that's what I was afraid of.
well, looks like I got my work cut out for me...

Kuhn is offline   Reply With Quote
Old 08-14-2018, 05:22 AM   #8
Kuhn
Newly Registered User
 
Join Date: Oct 2013
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Kuhn is on a distinguished road
Re: access migration to SQL

Quote:
Originally Posted by sonic8 View Post
So, you are searching the Recordset for a certain value. - Bad idea in a client-sever-environment. This forces all the data to be transferred to the client before your search can happen.

In a client-sever-environment you should try to let the server do most of the work by using SQL queries to filter down the data first. Only after that you would do additional client side processing on a small sub-set of the data. (If still required at all.)

thanks for that - I rewrote the code that selected the record, and filtered down the data using SQL as suggested. that did the trick!

also big thx to the person that posted that link to the migration tool - it will save me a lot of time, esp. index-wise !

Kuhn is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Migration of Access Web App ThomasVDS Access Web 1 03-02-2018 06:16 AM
Access migration and Mysql brmikwen General 1 11-24-2009 08:12 AM
Question Access Database migration rajesh876 General 2 08-06-2009 06:57 AM
Access 2k to 2003 migration juangonzalez898 General 6 06-12-2008 07:35 PM
Access migration Rockape General 1 03-05-2008 04:33 PM




All times are GMT -8. The time now is 09:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World