Create Table & Add Data

[catt]

Based on a prior post I created the following procedure, which creates a table (GREAT!!) but I can't get the data per the where statement to transfer over.

I need all the fields populated (name, social, term, sumofamt) where I choose the term and the amount. I was trying to start by choosing only one field (term) but nothing comes accross.


Private Sub Command3_Click()


Dim TableName As String

Dim strSQL As String

TableName = InputBox("Please type a table name, per semester")

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "Addchangename", TableName, True
MsgBox "Table " & TableName & " created"

strSQL = "INSERT INTO [" & TableName & "]SELECT AddChangeName.* From AddChangeName where (([AddChangeName]![term]= 200301));"

MsgBox "Data Transferred to " & TableName

End Sub

Any help is greatly appreciated.

Judy

 

[crosmill]

I think you might just need to execute the SQL

exec strSQL

 

[catt]

Hi Crosmill;

I tried typing "exec strSQL" after the strSQL line and I get a compile error. I added "exec" to the beginning of the strSQL line and I got a compile error.

I don't understand execute the SQL.???

What am I doing wrong?


Thank You,


Judy

 

[crosmill]

All your doing is setting a string to a variable, your not telling VBA to execute a command.

Just like
strSQL = "hello"

It doesn't do anything after that

A compile error is good, it should tell you what the problem in the SQL is?? maybe

try adding a space "] SELECT

I have to go now but post back if you can't get it going and I'll take another look in the morning.

Chris

 

[crosmill]

I took another look at your SQL and I've made a few ammends, I'm not sure if it'll be right though.

strSQL = "INSERT INTO [" & TableName & "] SELECT * FROM AddChangeName WHERE [AddChangeName]![term] = 200301"

NB. I changed capitals for my own personal preference. I don't think you need any brackets in there. And I've added a couple of spaces and rmoved a column name from the SELECT as you chose * anyway. You also had a full stop (I think you call it a period?) I'm not sure if that that was part of the Column name or a typo?

If your still getting an error post the error message. Let me know if it works.

HTH

 

[catt]

Hi Again;

It is probably so simple it's going over my head. But the new code doesn't work. I even tried it as a separate procedure and it still doesn't work.

Help Please!!!


Judy

 

[crosmill]

First thing to do is to make sure the SQL works.

Create a new query, and where you normally click the design/table view (top left) click the drop arrow and select SQL.

Paste your SQL into there and run it to see if it works. If it doesn't then that's the first thing you need to sort out.

Remember if you don't post any error messages I have no way to know where to start to look for errors.

If the SQL doesn't work then build the query with the query builder and then select the SQL option, the code will be there.

Chris

 

[catt]

Hi;

INSERT INTO ABABA ( SOC, NAME, SumOfAMT, TERM )
SELECT AddChangeName.SOC, AddChangeName.NAME, AddChangeName.SumOfAMT, AddChangeName.TERM
FROM AddChangeName
WHERE (((AddChangeName.TERM)="200301"));

This is the SQL I got from an append query. ABABA is the table created with the first procedure, which creates the new table & structure.

I added;

Dim strSQL as string

and strSQL = to the beginning of INSERT

with this I get "Compile error: Syntax error

So, I moved the SELECT statement to the end of the INSERT statement and I get an immeadiate "Compile error: Expected: end of statement.

So, now I moved the WHERE statement to the end of the FROM statement and the new line FROM.... to the end of the INSERT statement. Now I have a long line and I get a Compile error: Syntax error



Judy


P.S. The original post is "Whats wrong with this??!! by Sconly on Oct 17, 2002 - Maybe it will help?

 

[Pat Hartman]

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "YourQueryName", TableName, True
MsgBox "Table " & TableName & " created"

Replace - YourQueryName - with the name of a select query that selects the records that you want in the new table.

 

[catt]

Hi Pat:

I entered your code (copy paste) and placed it in the onclick command of a button on a form.


Private Sub Command0_Click()
Dim tablename As String


tablename = InputBox("please type a table name,")

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "query1", tablename, True
MsgBox "Table " & tablename & " created"

'Replace - YourQueryName - with the name of a select query that selects the records that you want in the new table. Help, please.



End Sub



I now have a new table with all the fields but still no data. I must be doing something wrong.



Judy

 

[Pat Hartman]

I didn't look at your TransferDatabase arguments. I presumed that you had. Silly me. The last argument is the one in error.

 

[Tim K.]

What about this?

Private Sub Command3_Click() 

Dim dbs As Database
Dim TableName As String 
Dim strSQL As String 

TableName = InputBox("Please type a table name, per semester") 

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "Addchangename", TableName, True 
MsgBox "Table " & TableName & " created" 

strSQL = "INSERT INTO [" & TableName & "] SELECT AddChangeName.* From AddChangeName where (([AddChangeName]![term]= 200301));" 

[COLOR=orangered]dbs.Execute strSQL[/COLOR] 

MsgBox "Data Transferred to " & TableName 

End Sub

 

[catt]

Dearest Pat:

I wish you had been more specific, but I looked up the explanation for each of the arguments in the transfer database command and LO and BEHOLD the last word should be false, not true.

Because I had copied the code from another post it didn't dawn on me that any of it was wrong.

By the way I had read on prior posts where you were asked to write a book, I will even take a draft.


Thank you Very much.


judy