access migration to SQL (1 Viewer)

Kuhn

Registered User.
Local time
Today, 07:43
Joined
Oct 21, 2013
Messages
17
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?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:43
Joined
Feb 19, 2013
Messages
16,555
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?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:43
Joined
Jan 20, 2009
Messages
12,849
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.
 

Kuhn

Registered User.
Local time
Today, 07:43
Joined
Oct 21, 2013
Messages
17
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.
 

sonic8

AWF VIP
Local time
Today, 15:43
Joined
Oct 27, 2015
Messages
998
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.)
 
Last edited:

Kuhn

Registered User.
Local time
Today, 07:43
Joined
Oct 21, 2013
Messages
17
thx, that's what I was afraid of.
well, looks like I got my work cut out for me...
 

Kuhn

Registered User.
Local time
Today, 07:43
Joined
Oct 21, 2013
Messages
17
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 !
 

Albert Dicosta

Registered User.
Local time
Today, 07:43
Joined
Sep 7, 2018
Messages
16
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.
 

kutatebi

New member
Local time
Today, 07:43
Joined
Oct 4, 2018
Messages
1
I also used the migration tool for this kind of job.
Migration was quick and successful.

I took DBConvert for Access & MS SQL converter.
 

Users who are viewing this thread

Top Bottom