Is there a way to create a table in Access fom a Recordset?
Printable View
Is there a way to create a table in Access fom a Recordset?
How are you getting the recordset in the first place?
Well, this way
Is there a way to create a NEW table fom that recordset without having to create all the fields?Code:Select * From TABLE
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
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
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 :wave:
Brandon.
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
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
'#######################################################################
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?
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)?
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!
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!