Create table in Access vba and populate with results from query

Question

I am working on an access 2010 database and needs some help.

I am trying to create some vba code that will run a query and then create and populate a table based on the records returned. I then want to rename the table based on a value from the query (company name).

I have the query built that will return distinct values from my table, but I need to create a seperate table for each company that is returned in the results.

Any and all suggestions are appreciated.

Do you need to create one table per run (all the records returned will be for a single company) or multiple tables (multiple companies in result set, need to create and populate a table for each one)? — Ann L.
– Ann L., Commented Oct 23, 2012 at 19:25

fthiella · Accepted Answer · 2012-10-23 19:55:29Z

2

Are you sure that it is a good idea to create a different table for every company? I would not reccommend that!

Okay let's say that you have your query, named QUERY, that contains the field CompanyName and contains all of the records. You could start with something like this:

Sub Export_Query()
  Dim rs As Recordset

  Set rs = CurrentDb.OpenRecordset("select distinct CompanyName from QUERY")
  While Not rs.EOF
    On Error Resume Next
    CurrentDb.Execute "DROP TABLE [" & rs("CompanyName") & "]"
    CurrentDb.Execute "SELECT QUERY.* INTO [" & rs("CompanyName") & "] FROM QUERY WHERE CompanyName=""" & rs("CompanyName") & """;"
    On Error GoTo 0
    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing
End Sub

This is just an idea... you should add more error checking code, and you should also escape company name or make sure it doesn't contain any special character.

edited Oct 23, 2012 at 19:55

answered Oct 23, 2012 at 19:41

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Conklin Over a year ago

This is exactly what I was looking for. Thanks!! It works great for what I need to do. I have to create a separate table for each company for another department to load the data into our SQL database. I don't know why I can't just give them all the data in one table and they load it, but at this point I just want to get them what they need for now.

Collectives™ on Stack Overflow

Create table in Access vba and populate with results from query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related