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: 17
Thanks: 5
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,352
Thanks: 39
Thanked 3,348 Times in 3,243 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 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,251
Thanks: 74
Thanked 1,403 Times in 1,324 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 online now   Reply With Quote
Old 08-10-2018, 06:04 AM   #4
AccessBlaster
.
 
Join Date: May 2010
Posts: 860
Thanks: 19
Thanked 233 Times in 223 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 offline   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: 17
Thanks: 5
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: 117
Thanks: 22
Thanked 36 Times in 35 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.)
__________________
I just released a new video that's a bit different. Not a tutorial in the true sense, just me coding some slightly advanced VBA. - Let me know what you think.

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: 17
Thanks: 5
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: 17
Thanks: 5
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
Old 09-07-2018, 12:35 AM   #9
Albert Dicosta
Newly Registered User
 
Join Date: Sep 2018
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Albert Dicosta is on a distinguished road
Re: access migration to SQL

Please follow these steps to access migration to SQL:

1. Open the database in Microsoft Access.

2. Choose the Database Tools tab in the Ribbon.

3. Click the SQL Server button located in the Move Data section. This opens the Upsizing Wizard.

4. Select whether you want to import the data into an existing database or create a new database for the data. For this tutorial, assume that you're trying to create a new SQL Server database using the data in your Access database. Click Next to continue.

5. Provide the connection information for the SQL Server installation. You'll need to provide the name of the server, credentials for an administrator with permission to create a database and the name of the database you want to connect. Click Next after providing this information.

6. Use the arrow buttons to move the tables you want to transfer to the list labeled Export to SQL Server. Click the Next button to continue.

7. Review the default attributes that will be transferred and make any changes desired. You have the option to preserve settings for table indexes, validation rules, and relationships, among other settings. When done, click the Next button to continue.

8. Decide how you want to handle your Access application. You may choose to create a new Access client/server application that accesses the SQL Server database, modify your existing application to reference the data stored on SQL Server, or copy the data without making any changes to your Access database.

9. Click Finish and wait for the upsizing process to complete. When you are finished, review the upsizing report for important information about the database migration.

I have tested these steps on my project.
Albert Dicosta is offline   Reply With Quote
Old 10-04-2018, 02:57 AM   #10
kutatebi
Newly Registered User
 
Join Date: Oct 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
kutatebi is on a distinguished road
Re: access migration to SQL

I also used the migration tool for this kind of job.
Migration was quick and successful.

I took DBConvert for Access & MS SQL converter.

kutatebi 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 07:00 PM.


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