Create Table - Add Data 1

[tbassngal]

Does someone have a copy of some syntax on how to create a table using VBA? I have three fields I want to write to a new table:

ProductionPOId, ProdQty, ProdDate

This is what I have so far:

Public Sub CreateTableTest()
CREATE TABLE tblProdSched
(ProjectPOId text, ProdQty LONG, ProdDate LONG);
End Sub

Then, how would I add values to this table through code? Say I have the value for ProjectPOId, then strProdQty, strProdDate? Any examples you could offer would be very helpful, I think the last post I put out there was too long and confusing.

 

[flyover789]

something like that?

Public Sub CreateTableTest(ProjectPOId As String, lngProdQty As Long, dtProdDate As Date)

DoCmd.RunSQL "CREATE TABLE tblProdSched " & _
"(ProjectPOId  text, ProdQty LONG, ProdDate LONG);"
DoCmd.RunSQL "INSERT INTO tblProdSched (ProjectPOId, ProdQty, ProdDate) " & _
"SELECT '" & ProjectPOId & "', " & lngProdQty & ", #" & dtProdDate & "#;"

End Sub

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[PHV]

Try something like this:
Public Sub CreateTableTest()
DoCmd.RunSQL "CREATE TABLE tblProdSched (ProjectPOId text(10), ProdQty LONG, ProdDate DateTime)"
CurrentDB.TableDefs.Refresh
End Sub

DoCmd.RunSQL "INSERT INTO tblProdSched (ProjectPOId, ProdQty, ProdDate) VALUES (" _
& "'" & ProjectPOId & "'" _
& "," & strProdQty _
& ",#" & strDate & "#)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tbassngal]

Thanks to the both of you for your help, PVH: it worked! At least creating the table worked - I don't have anything to add to the table just yet, but when I finally figure out that piece, hopefully, this will help. Thank you to both of you so much!