How to create a new Table using code (1 Viewer)

[WilRel]

Hi again

I would like to create a New Table [NTable] based on a Existing Table [ETable]. I can do so with a MAKE_Table Query but I would like to do so in CODE - to minimize the amount of Queries and to understand the use of code and the commands/methods involved.

I did try to look in the help file and found the use of a method called OpenRecordset but I'm not sure if is the right one to use and it gives me the error "Can't find project or library" when used (Error msg higtihgt the word "OpenRecordset")

Here is what I've been trying:

  Sub OpenMyRecordset()

     Dim NewRST As Recordset

     Set NewRST = OpenRecordset("ETable")
     NewSRT.Close
   
  End Sub

I am really not sure how to do it. First I would just like to create a new table/recordset that is the same as the old one, just to see if my syntax is correct and then try to add a filter.

Say ETable have four fields (Name, Surname, Number and BirthDate)

Just to understand the syntax, I would like your help on creating the new Table [NTable] so that it only contains the fields Name, Surname and Number of only all those entries with Names starting with say "B"

I would greatly appreciate your help

Regards WR

 

[plog]

Whatever you can do in a query, you can do in VBA by using the SQL of that query and executing it.

To learn how to do this, I would create your make table query, go into the SQL view of that query once its created, copy the code, create a VBA function that uses that code. Specifically you will want to use the DoCMD.RunSQL method.

Play with that and you should see what I mean. If you need more guidance let me know.

 

[WilRel]

Hi Plog

Thanks for your reply. I did try out a few things and found out a bit more about the structure and syntax. I do appreciate.

What I struggling now is how to show this new table in a form, displaying in continious form view.

 

[WilRel]

What I did was to create the form, but everytime I call it via code, it only displays one empty line, but when I call it manualy, it shows all the records.

I presume its syntax again, but at this moment, my day-job keeps me a bit busy.

thanx again

regards WR

 

[missinglinq]

What is the exact code you're using to call the Form? Calling a Form thru code can be tricky for the uninitiated. For instance, using code such as

DoCmd.OpenForm "YourFormName", , , , acFormAdd

will open the Form as if it's DataEntry Property were set to Yes, meaning that new Records can be added, but existing Records, from previous sessions, will not be displayed. And this will override how the Property is set inside the Form itself.

So opening it independently, by clicking on its name, will show all Records, but calling it in this manner will not.

Linq ;0)>

 

[RainLover]

Bob Lason has written some tools that may be of help to you.

You could copy his code and use it for your own situation.

http://www.btabdevelopment.com/ts/

 

[WilRel]

Hi MLinq

I'm using the code as follows:

DoCmd.OpenForm "MyForm", acNormal, , , acFormAdd, acDialog

I found this example somehow, but I'm not sure what it all means...