Upsizing Wizard with VBA

[Carly]

I would like to be able to push data into a SQL table using VBA as opposed to the Upsizing Wizard.

Is this possible?

Kind Regards

Carly

 

[Galaxiom]

It is if you are fluent in SQL and VBA.

If you know the design of the SQL tables just send the commands to the server to create them.

If you want to actually convert an arbitrary database you will need to be able to read all the properties of the tables, fields and indexes in Access and then construct commands with this information to recreate tables and set field properties in SQL.

Then you need to modify all the queries in Access to read from the SQL server.

 

[Carly]

I wouldnt say I was fluent in either, but I do know quite a lot of both.

I guess by your response it means there is no easy way of just running through the wizard in VBA?

Kind Regards

Carly

 

[Galaxiom]

Ah. Triggering the Wizard from VBA. I don't know about that.
I have never actually used that Wizard. To be honest I don't entirely trust any of them.

When I am using SQL Server I construct the tables in the Management Studio interface.

 

[boblarson]

Big question - WHY would you need to do this in VBA? It should be a ONE TIME DEAL. Are you planning on upsizing a bunch of databases?

Also, the SSMA (SQL Server Migration Assistant) is better than the Upsizing Wizard in Access and, depending on which version of Access and SQL Server you are using, you may have to use that anyway.

 

[Carly]

Thanks for the replies...all I need to do what the following, I didnt need to upsize everytime...

CurrentDb.Execute "DELETE FROM [ODBC;Driver=SQL Server;Server=Hornbill2;DATABASE=Business Intelligence;Trusted_Connection=Yes].dbo.zCustomerDataMaster", dbSeeChanges
CurrentDb.Execute "INSERT INTO [ODBC;Driver=SQL Server;Server=Hornbill2;DATABASE=Business Intelligence;Trusted_Connection=Yes].dbo.zCustomerDataMaster " & _
        "SELECT * FROM zCustomerDataMaster"

Kind Regards
Carly