Migrating the backend to SQL: error message when using the Migration Assistant

Question

I tried bothe 32-bit and 64-bit versions of the SQL Server Migration Assistant for Access (AccessToSQL):

I get the following error message:

80040154 Class not registered (exception from H result: 0 × 80040154 (regDB_E_classnotreg)). This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. You can run 32-bit  SSMA application if you have a 32-bit connectivity components or 64-bit application if you have 64-bit connectivity components. Shortcut to both 32 bit and 64 bit SSMA can be found under the programs menu.

Any solutions?

Thanks,

Al

Answer 1

Sir, I maybe not follow you well.

You said you can link the table from SQLServer with ODBC, OK?

So you can create table in SqlServer with several ways such as run Storage Process or you can run the sql statement in scripts directly as below in access vba evironment.

I am not sure what you want to do in fact?

I guess you want to design sql server database build a table or a storage process or view by Access Tools(as the assistant immigaration for ADP). O,I seldom design ADP like this way.

Private Sub createtblInventory()

Dim db As Database

Dim rs As Recordset

Dim strConnect As String

Dim strSql As String

Dim strResult As String

strConnect = "ODBC;DSN=Match20140810"

'Open the database

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, _

strConnect)

' strSql = "sp_helpdb"

strSql = "if exists (select * from sysobjects where id = object_id('dbo.tblInventory')) drop table tblInventory"

db.Execute strSql, dbSQLPassThrough

strSql = "CREATE TABLE tblInventory (InvID INTEGER,ItemNo TEXT,CoverDueUncoverDue TEXT)"

db.Execute strSql, dbSQLPassThrough

db.Close

End Sub

Answer 2

ciao Alconte,

I did not face this issue, and migration assistance works well in my configuration.

anyway, it seems installing data connectivity components the problem could be fixed :

https://www.microsoft.com/en-us/download/details.aspx?id=13255

get a look here :

• Microsoft SQL Server Migration Assistant for Access Issue on 64bit Systems
• SQL Server Migration Assistant for Access nightmare

let us know!

Ciao, Sandro.

Thank you. I asked the person on the second link if he had any adverse effects from installing  the Microsoft Access Database Engine 2010 Redistributable.

As I said in a comment there, I have many other mission critical DBs that do  not need an SQL backend. Will be developing another important one, and do not want problems with them. 

Thanks,

Al

Answer 3

ciao Alconte,

I did not face this issue, and migration assistance works well in my configuration.

anyway, it seems installing data connectivity components the problem could be fixed :

https://www.microsoft.com/en-us/download/details.aspx?id=13255

get a look here :

• Microsoft SQL Server Migration Assistant for Access Issue on 64bit Systems
• SQL Server Migration Assistant for Access nightmare

let us know!

Ciao, Sandro.

Answer 4

Install ODBC or the  SQL Server Migration Assistant for Access (AccessToSQL)?

If the latter (AccessToSQL), I already have the 32-bit version and that does not work, either.

As far as ODBC is concerned, using the External Data > ODBC Database menu item, I am able to connect to existing tables without any problem.  "ODBC Database" uses my existing ODBC connection without any problem.

I was hoping that the AccessToSQL would take an Access DB and create the table(s) for me...

Thanks,

Al

Answer 5

Yes, 64bit ODBCmaybe not compatible. Install 32bit only