How to split the code in VBA

[Kiranpendy]

I have the following code that i need to write in vb after button click but i'm not able to write it because it is too big. I know that you could use "_" or "& _" for strings. But these are fields columns in the table and i'm pretty new to MS Access. Any help is appreciated.

The Sample code:

CurrentDb.Execute "INSERT INTO Table1( Field 1,Field 2, [Field 3],[Field 4], ....
....
.,Field 40]) 
SELECT Tabel2.Field1, Tabel2.Field2, Tabel2.[Field3],..., 
..,
..,Table2.[Field 40]
FROM Table2"

 

[theDBguy]

Hi. Welcome to AWF!


You should still be able to use the continuation characters for SQL statements. For example:

strSQL = "INSERT INTO TableName(Field1, " _
    & " Field2, " _
    & " Field3, " _
    & and so on...

 

[Kiranpendy]

Thank you! it worked fine.

 

[theDBguy]

Thank you! it worked fine.

Hi. You're welcome. Glad to hear you got it to work. Good luck with your project.

 

[Kiranpendy]

Thank you!

 

[Uncle Gizmo]

Something else you can do to tidy up your code is to "Take Out The Tables" If you have a single table, it's not clear from the code snippet you've posted, but it appears that you only have one table.

SELECT Tabel2.Field1, Tabel2.Field2, Tabel2.[Field3],.....,Table2.[Field 40]
FROM Table2

You can remove the references to the table name your SQL Statement is based on, "IF" it's a single table. This trick depends on NOT just selecting the table name, but also the DOT "." following it. You need to select the dot (.) to prevent your action removing the actual reference to the table in the FROM Clause.

Your new SQL Statement would look something like this:-

SELECT Field1, Field2, [Field3],.....,[Field 40]
FROM Table2

I explain it in this YouTube video here:- https://youtu.be/IorNg7ROemE?t=105 the video should start at the correct place but if not, the Time Index is about 1 minute 40 seconds into the video.

There's more tips like that one on my Nifty Access website here:- https://www.niftyaccess.com/nifty-tips/