Mohamed ElSheikh
asked on
ADODB.Recordset to new access table same fields names, types and size
Hi there
i would like to create a function that create a table in (Access database) with the same fields name/type/size of the ADODB.Recordset that i passed to the function.
Also if the table is exists, then we need to check all the fields are exists too with the same types, otherwise update the table to match the recordset
the function will look something like that
Public Function NewRSTable(rs As ADODB.Recordset, cn ad ADODB.Connection, Optional OutputTableName As String) as Boolean
if database in cn is not exist
create empty database using same cn path
else
1- check if OutputTableName table exist - match all fileds with the rs otherwise, update the table definition to match the RS (or we can simply delete the table and re-create it again)
2- create a new table using same field name/type/size in the recordset
end if
return yes/no and why not
i hope you understand what i mean
kind regards,
Mohamed
i would like to create a function that create a table in (Access database) with the same fields name/type/size of the ADODB.Recordset that i passed to the function.
Also if the table is exists, then we need to check all the fields are exists too with the same types, otherwise update the table to match the recordset
the function will look something like that
Public Function NewRSTable(rs As ADODB.Recordset, cn ad ADODB.Connection, Optional OutputTableName As String) as Boolean
if database in cn is not exist
create empty database using same cn path
else
1- check if OutputTableName table exist - match all fileds with the rs otherwise, update the table definition to match the RS (or we can simply delete the table and re-create it again)
2- create a new table using same field name/type/size in the recordset
end if
return yes/no and why not
i hope you understand what i mean
kind regards,
Mohamed
ASKER
Thank you for your feedback
RS contains data from different tables and i want this function to be re-useable for different tools
thanks
Mohamed
RS contains data from different tables and i want this function to be re-useable for different tools
thanks
Mohamed
OK, then lets see what other Exerts may contribute.
That's a pretty big request on a volunteer help forum ...
You can create a database using several methods. One is via the DAO.CreateDatabase method:
DAO.CreateDatabase "Full path to the db" , dbLangGeneral, dbVersion120
Here: https://msdn.microsoft.com/en-us/library/office/ff835033%28v=office.15%29.aspx
You can check whether it exists by using DIR. After parsing it from the Connection:
If Dir("YourPath") <> "" Then
'/ it exists
Else
'/ it does not exist
End If
To create the table, you'd have to loop through the Fields collection of the Recordset:
Dim fld As ADODB.Field
For each fld in rst.Fields
'/ add the column to a table
Next fld
You can create a database using several methods. One is via the DAO.CreateDatabase method:
DAO.CreateDatabase "Full path to the db" , dbLangGeneral, dbVersion120
Here: https://msdn.microsoft.com/en-us/library/office/ff835033%28v=office.15%29.aspx
You can check whether it exists by using DIR. After parsing it from the Connection:
If Dir("YourPath") <> "" Then
'/ it exists
Else
'/ it does not exist
End If
To create the table, you'd have to loop through the Fields collection of the Recordset:
Dim fld As ADODB.Field
For each fld in rst.Fields
'/ add the column to a table
Next fld
ASKER
Hi Scott McDaniel,
thank you for your reply
how can we add fields to the new table to be similar (name/type/size) to fields in recordset
I am expecting the code to read the fields definitions in recordset and create same field definitions into the new table
I am able to loop in the RS fields and get the fields names. but to get the types it returns different types like "string" instead of "text". and i am not sure about the other types
Thanks
thank you for your reply
how can we add fields to the new table to be similar (name/type/size) to fields in recordset
I am expecting the code to read the fields definitions in recordset and create same field definitions into the new table
I am able to loop in the RS fields and get the fields names. but to get the types it returns different types like "string" instead of "text". and i am not sure about the other types
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
'Copy the table to a new table based on he names in two textboxes
DoCmd.CopyObject , Me.txtNewTableName, acTable, Me.txtTableName
'Clear all the records from the new table.
CurrentDb.Execute "DELETE * FROM " & txtNewTableName, dbFailOnError
JeffCoachman