[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ADODB.Recordset to new access table same fields names, types and size

Posted on 2015-02-05
8
Medium Priority
?
72 Views
Last Modified: 2016-07-12
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
0
Comment
Question by:Mohamed ElSheikh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40591901
Why not just copy the table, then clear it out?

'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
0
 

Author Comment

by:Mohamed ElSheikh
ID: 40591942
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40592031
OK, then lets see what other Exerts may contribute.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 85
ID: 40592506
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
0
 

Author Comment

by:Mohamed ElSheikh
ID: 40592660
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
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40593282
The ADO Field object would give you everything needed. The Field.Type would tell you the datatype and Field.DefinedSize would tell you the Length, in the case of Text field.

To add fields, you'd first add a Table, then append Fields to that Table:

Dim tdf As DAO.TableDef
Set tdfNew = CurrentDB.CreateTableDef("YourTableName")
tdf.Fields.Append tdf.CreateField(fld.Name, fld.Type, fld.DefinedSize)

I'm not sure if the ADO Field.Type will translate over to the DAO Field.Type, but you can give it a shot.

Note this does not create indexes, primary keys, or anything of that sort. This would just create a table with columns of a specific data type.

DAO CreateTableDef: https://msdn.microsoft.com/en-us/library/office/ff835094.aspx
DAO CreateField: https://msdn.microsoft.com/EN-US/library/office/ff821396.aspx
ADODB Field Object: http://www.experts-exchange.com/Database/MS_Access/Q_28611073.html#a40592660
0
 

Author Comment

by:Mohamed ElSheikh
ID: 40753951
Thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question