SQLScripts

[Gopinath R]

Hello All,

I'm a SQLServer person and was asked to work on an Access project.

In the project, we are supposed to do two things :

[1] Convert the Database from Access 2 to Access 2003
[2] Migrate the Access 2 from the Old Data Model (Old database(s)) to the
New Data Model (a single DB).

Since we have to move the data from the Old Data Model (Access 2) to the
New Data Model, we decided
to not use the Access 2 upgrade tool and instead write a script that would
pull data from the Access 2 (Old DM)
and insert into Access 2003 (New DM).

In SQLServer, I would script the logic, in T-SQL, and run it from a batch
file. But I dont know how to do this
in Access since I have never worked in Access before. Also, the script needs
to be executed (deployed) at 200
sites as well.

Would anyone know how to do this ?

Thanks,
rgn

 

[John Nurick]

Hi RGN,

Presumably the "new model" Access 2003 MDB file will contain the (empty)
tables and relationships and it's just a matter of appending the data
from the old MDBs to the new tables.

If you want to do that from a batch file you can use a VBScript (sample
below). Alternatively you could put VBA code into the new MDB and run it
from there. Either way the general idea is to create an object that can
execute Jet "action queries" and then feed it, one by one, Jet SQL
statements that do the work of the T-SQL script you have in mind.

'Sample VBScript to import data from one MDB file
'to another without opening Access

Option Explicit

Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("D:\Folder\New.mdb")

strSQL ="INSERT INTO tblB SELECT * FROM tblA IN 'D:\Folder\Old.mdb';"
oDB.Execute strSQL
strSQL = "SOMETHING ELSE"
oDB.Execute strSQL
....

oDB.Close

Hello All,

I'm a SQLServer person and was asked to work on an Access project.

In the project, we are supposed to do two things :

[1] Convert the Database from Access 2 to Access 2003
[2] Migrate the Access 2 from the Old Data Model (Old database(s)) to the
New Data Model (a single DB).

Since we have to move the data from the Old Data Model (Access 2) to the
New Data Model, we decided
to not use the Access 2 upgrade tool and instead write a script that would
pull data from the Access 2 (Old DM)
and insert into Access 2003 (New DM).

In SQLServer, I would script the logic, in T-SQL, and run it from a batch
file. But I dont know how to do this
in Access since I have never worked in Access before. Also, the script needs
to be executed (deployed) at 200
sites as well.

Would anyone know how to do this ?

Thanks,
rgn