|
 |
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?
|
|
|
View Cart
