-
Oct 2nd, 2003, 03:19 PM
#1
Thread Starter
Addicted Member
Table from Recordset
Is there a way to create a table in Access fom a Recordset?
-
Oct 3rd, 2003, 12:38 AM
#2
How are you getting the recordset in the first place?
-
Oct 3rd, 2003, 07:00 AM
#3
Thread Starter
Addicted Member
Well, this way
Code:
Select * From TABLE
Is there a way to create a NEW table fom that recordset without having to create all the fields?
-
Oct 3rd, 2003, 09:47 AM
#4
Hyperactive Member
Do you have to use a recordset?
Instead can you just copy directly into the new table like:
Code:
Select * INTO <New_Table> From TABLE
-
Jun 30th, 2004, 08:28 AM
#5
Hyperactive Member
Create Access table from ADO recordset?
So, is there a way to dump an ADO recordset into a new Access table?
Scenario:
I'm in Access VBA,
create a recordset from various data sources (doesn't matter what),
do all kinds of manipulations, adjustments, tweaks, adds, deletes etc.
Now I want to create a new, local Access table from this.
This sounds like a reasonable thing to do.
Thanks, DaveBo
"The wise man doesn't know all the answers, but he knows where to find them."
VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15
-
Jun 30th, 2004, 09:49 AM
#6
Addicted Member
eghhh... I don't think you can do it that way...
BUT more reasonable to do is:
1) Use SELECT .... INTO [NewTable] FROM [Bunch of Tables]
>>> this way you create your new table
2) Now play with the new table just as you did with th recordset (adjustments, tweaks, etc.)
How about that
Brandon.
-
Jul 1st, 2004, 08:14 AM
#7
Hyperactive Member
Thanks Brandon
That's certainly one way to do it.
I do a lot of data manipulation in VBA with recordsets and just thought that if you can move data from a table to a recordset there should be a simple way to move the data from a recordset to a (new) table, retaining all field properties/data types.
Maybe I'm just missing something, my aim has always been awful. DaveBo
"The wise man doesn't know all the answers, but he knows where to find them."
VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15
-
Jul 1st, 2004, 08:57 AM
#8
Hyperactive Member
An attempt manually building an Access table from a recordset
For your enjoyment, critique, correction, improvement, guffaws, slings and arrows, etc.
Note, the part I left out here, and the main assumption behind my broaching this, is that the recordset is undergoing a LOT of manipulation before being written out. i.e. we're not talking about a simple table copy. Have fun, DaveBo
Code:
Sub Test_MakeTableFromADO_Recordset()
Const NewTableName = "MyNewTable"
Dim con As ADODB.Connection
Dim DAO_Type As Integer
Dim db As Database
Dim fld As DAO.Field
Dim FldNo As Integer
Dim fName As String
Dim fSize As Long
Dim fType As Integer
Dim rst As New ADODB.Recordset
Dim strQuery As String
Dim tdfNew As TableDef
Dim x As Long
Set db = CurrentDb() ' This is just to get at the TableDefs
Set con = CurrentProject.Connection
Set rst.ActiveConnection = con
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseClient
rst.LockType = adLockReadOnly
rst.Open "Select * from DataTypesSamples" ' Just a test case to get some data into rst
' Create the new table structure & define all fields from the recordset properties
Delete_Table NewTableName
Set tdfNew = db.CreateTableDef(NewTableName) ' Create new TableDef object.
Debug.Print
For FldNo = 0 To rst.Fields.Count - 1
fName = rst.Fields(FldNo).Name
fSize = rst.Fields(FldNo).DefinedSize
fType = rst.Fields(FldNo).Type
' When creating the new fields in TableDef need to crossref
' the recordset's ADO field type with the DAO type
'?? Note, we're not carrying over field Attributes like dbHyperlinkField
'?? Also, not accounting for OLE Objects
Select Case fType ' Convert the ADO type to DAO type
Case adInteger: DAO_Type = 4 ' dbLong
Case adUnsignedTinyInt: DAO_Type = 2 ' dbByte
Case adCurrency: DAO_Type = 5 ' dbCurrency
Case adDate: DAO_Type = 8 ' dbDate
Case adNumeric: DAO_Type = 20 ' dbDecimal
Case adSmallInt: DAO_Type = 3 ' dbInteger
Case adSingle: DAO_Type = 6 ' dbSingle
Case adDouble: DAO_Type = 7 ' dbDouble
Case adLongVarWChar: DAO_Type = 12 ' dbMemo & Hyperlink
Case adVarWChar: DAO_Type = 10 ' dbText
Case adBoolean: DAO_Type = 1 ' dbBoolean
End Select
Debug.Print fName, fType, DAO_Type, fSize ' show the field info
If DAO_Type <> 20 Then
'?? it doesn't like dbDecimal - Run-time error '3259': "Invalid field data type"
Set fld = tdfNew.CreateField(fName, DAO_Type, fSize)
tdfNew.Fields.Append fld
End If
Next
db.TableDefs.Append tdfNew ' Add this new table structure into TableDefs
Set fld = Nothing
Set tdfNew = Nothing
Set db = Nothing
' Load the recordset's data into new table
For x = 1 To rst.RecordCount
' loop on recordset getting each record and building up
' an INSERT statement for each. Seems like hard way.
strQuery = "Insert Into " & NewTableName & " Values("
For FldNo = 0 To rst.Fields.Count - 1
If IsNull(rst.Fields(FldNo).Value) Then
strQuery = strQuery & "NULL,"
Else
Select Case rst.Fields(FldNo).Type
Case adNumeric ' skip this (decimal), since we skipped it above
'strQuery = strQuery & rst.Fields(FldNo).Value & ","
Case adVarWChar, adDate, adLongVarWChar ' Surround these with single quotes
strQuery = strQuery & "'" & rst.Fields(FldNo).Value & "',"
Case Else
strQuery = strQuery & rst.Fields(FldNo).Value & ","
End Select
End If
Next
' Remove last comma and add ")" to close the Values() clause
strQuery = Left$(strQuery, Len(strQuery) - 1) & ")"
Debug.Print strQuery
con.Execute strQuery ' add the record
rst.MoveNext
Next
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
End Sub ' Test_MakeTableFromADO_Recordset
'#######################################################################
"The wise man doesn't know all the answers, but he knows where to find them."
VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15
-
Jul 1st, 2004, 09:30 AM
#9
Addicted Member
Hmmm... ok, I see your point. But I still don't understand why copying the two tables using SQL would not work for you?
Why are you converting fields from ADO to DAO types, if it is still the same database?... I am confused on this.
> and a little comment to the code: I would use "Do...Loop Until EOF" instead of For...Next loop, since if your table is very large, the RecordCount may not actually give you all your records! (unless you do MoveLast, then RecordCount, and then MoveFirst). The "Do" loop is so much better for this
OK, back to your problem... How about doing all the conversion in SQL? I mean changing the datatypes while reading the source table... OK, I better give an example:
SELECT CDbl(Field1), CInt(Field2), ... INTO [NewTableName] FROM [SourceTableName]
Would that work?
-
Jan 12th, 2005, 03:49 PM
#10
Hyperactive Member
Slightly different twist
I've got a corrupt Access 2000 MDB file.
Getting "'AOIndex' is not an index in this table." whenever I try to open the DB or even try to import from or link to it from another MDB.
However, I can access the tables in the corrupt DB from VB/VBA via ADODB and read whatever I like into a recordset.
Anyone know of a simple way to dump that recordset to a local Access table (besides the routine I provided above)?
"The wise man doesn't know all the answers, but he knows where to find them."
VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15
-
Jul 20th, 2005, 03:37 PM
#11
Frenzied Member
Re: Table from Recordset
Sorry to resurrect this ancient thread, but hey, it's EXACTLY" what I'm trying to do!
I am trying to create a Table in Access in the "Tables" Tab on my local (Client) PC from a remote Oracle Server database. You can NOT use the SQL "INTO" function with a Pass-Through SQL command if you can't create a table on the Server (which I definitely can NOT).
Thanks for your persistence, DaveBo! What a headache just to create a snapshot of a remote database!!! I hope it works for me too!
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
-
Jul 22nd, 2005, 10:36 AM
#12
Frenzied Member
Re: Table from Recordset
Thanks DaveBo!
Your approach works great! I got my test program working! I can now download a Pass-Through Query of an Oracle database directly into an Access Table!
Your process works great ...
1) Make a connection to the Oracle data base as a RecordSet.
2) Create and append an Access Table with the correct exact field structure in the local (Client) Database.
3) Loop through the returned Oracle Records fetching the desired fields and parsing them into an Access SQL Text String "INSERT INTO test_table VALUES(val1, val2, etc.)". Do a "myDatabase.Execute SQLstring" to load the data values into the local table.
4) Close everything out and clean house.
Thanks again!
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|