p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Dumping an MDB table to SQL database


Message #1 by "Mavin Specimen" <mspecimen@h...> on Wed, 06 Jun 2001 22:38:01
Does anyone know how to create a module in in an ADP that will cylce 

through an mdb table and dump it into a SQL database?



Thoughts appreciated.



Thanks



Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 07 Jun 2001 07:20:13 -0700
I don't believe you can link tables in an ADP, which is how I would want to

approach it.  If it was me, I'd link the SQL server tables in the .mdb (via

ODBC), and then use Access append queries to move the data into SQL server.



Hope that's helpful.



-Roy



-----Original Message-----

From: Mavin Specimen [mailto:mspecimen@h...]

Sent: Wednesday, June 06, 2001 3:37 PM

To: Access

Subject: [access] Dumping an MDB table to SQL database





Does anyone know how to create a module in in an ADP that will cylce 

through an mdb table and dump it into a SQL database?



Thoughts appreciated.



Thanks

Message #3 by "Mavin Specimen" <mspecimen@h...> on Thu, 07 Jun 2001 17:29:12
So far I am able to cycle through both recordsets but its now just a matter of somehow dumping that
Access mdb data into the SQL table. Here is some of the

code. rs is the Access ADO recordset, rst is the SQL recordset. I am trying to loop through the ADO recodset and dump that data into
empty tables in SQL. The

comments you see after some of the code is the error messages I am getting.



Dim fld As Field

rs.MoveFirst

Do Until rs.EOF

For Each fld In rs.Fields

    rst.CursorType = adOpenKeyset

    rst.LockType = adLockOptimistic

    rst.Open "tbl_Layout", cnn, , , adCmdTable

      

    rst![LayoutID] = rs.Fields("LayoutID").Value ' Multi- step operation generated erros. Check each OLE DB status value if
available. No work was done.

    



    rst![Layout] = rs.Fields("Layout").Value

    rst![LayoutSort] = rs.Fields("LayoutSort").Value

    rst.Update

Debug.Print fld.Value

Next

rs.MoveNext

Loop

**** Any help would be appreciated !! Thanks



>From: "Pardee, Roy E" 

>Reply-To: "Access" 

>To: "Access" 

>Subject: [access] RE: Dumping an MDB table to SQL database 

>Date: Thu, 07 Jun 2001 07:20:13 -0700 

> 

>I don't believe you can link tables in an ADP, which is how I would want to 

>approach it. If it was me, I'd link the SQL server tables in the .mdb (via 

>ODBC), and then use Access append queries to move the data into SQL server. 

> 

>Hope that's helpful. 

> 

>-Roy 

> 

>-----Original Message----- 

>From: Mavin Specimen [mailto:mspecimen@h...] 

>Sent: Wednesday, June 06, 2001 3:37 PM 

>To: Access 

>Subject: [access] Dumping an MDB table to SQL database 

> 

> 

>Does anyone know how to create a module in in an ADP that will cylce 

>through an mdb table and dump it into a SQL database? 

> 

>Thoughts appreciated. 

> 

>Thanks 
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 07 Jun 2001 11:02:21 -0700
You might need to add a call to rst.AddNew at the top of your Do Until loop.

You should also probably take the rst.open stuff out of that loop--you only

need to do that once.



-----Original Message-----

From: Mavin Specimen [mailto:mspecimen@h...]

Sent: Thursday, June 07, 2001 10:46 AM

To: Access

Subject: [access] RE: Dumping an MDB table to SQL database





So far I am able to cycle through both recordsets but its now just a matter

of somehow dumping that Access mdb data into the SQL table. Here is some of

the

code. rs is the Access ADO recordset, rst is the SQL recordset. I am trying

to loop through the ADO recodset and dump that data into empty tables in

SQL. The

comments you see after some of the code is the error messages I am getting.



Dim fld As Field

rs.MoveFirst

Do Until rs.EOF

For Each fld In rs.Fields

    rst.CursorType = adOpenKeyset

    rst.LockType = adLockOptimistic

    rst.Open "tbl_Layout", cnn, , , adCmdTable

      

    rst![LayoutID] = rs.Fields("LayoutID").Value ' Multi- step operation

generated erros. Check each OLE DB status value if available. No work was

done.

    



    rst![Layout] = rs.Fields("Layout").Value

    rst![LayoutSort] = rs.Fields("LayoutSort").Value

    rst.Update

Debug.Print fld.Value

Next

rs.MoveNext

Loop

**** Any help would be appreciated !! Thanks



>From: "Pardee, Roy E" 

>Reply-To: "Access" 

>To: "Access" 

>Subject: [access] RE: Dumping an MDB table to SQL database 

>Date: Thu, 07 Jun 2001 07:20:13 -0700 

> 

>I don't believe you can link tables in an ADP, which is how I would want to



>approach it. If it was me, I'd link the SQL server tables in the .mdb (via 

>ODBC), and then use Access append queries to move the data into SQL server.



> 

>Hope that's helpful. 

> 

>-Roy 

> 

>-----Original Message----- 

>From: Mavin Specimen [mailto:mspecimen@h...] 

>Sent: Wednesday, June 06, 2001 3:37 PM 

>To: Access 

>Subject: [access] Dumping an MDB table to SQL database 

> 

> 

>Does anyone know how to create a module in in an ADP that will cylce 

>through an mdb table and dump it into a SQL database? 

> 

>Thoughts appreciated. 

> 

>Thanks 



Message #5 by tony.scott@n... on Thu, 7 Jun 2001 19:59:47
> Does anyone know how to create a module in in an ADP that will cylce 

> through an mdb table and dump it into a SQL database?

> 

> Thoughts appreciated.

> 

> Thanks

> 







  Do you simply require the code to call?


  Return to Index