Access 2003 VBA Coding Question

I need help with code sample to get the min(system_creation_date) from Table 2 where the acct_nbr in Table 2 = the acct_nbr in Table 1.  Once that data is located it then needs to be inserted into Table 3. Table 1 has 65,000 records so I need to loop through until complete. I am new at this so if there is a better way to do it please let me know. I have taken code from an old update statement to get started. acct_nbr is a number data type and system_creation_date is a date data type. What I have tried so far is giving a syntax error.
Code so far:
Private Sub GetADDRNAMELINKINFO()

    Set db = CurrentDb()

    strsql = "SELECT * FROM [Tbl: Individual Data]"
    Set rst1 = db.OpenRecordset(strsql, dbOpenDynaset)
   
    If rst1.BOF And rst1.EOF Then
        'Do Nothing
    Else
        rst1.MoveLast
        lineCount = rst1.RecordCount
        rst1.MoveFirst
        retVal = SysCmd(acSysCmdInitMeter, "Matching Against Charge Table: ", lineCount)
        lineProgress = 1
               
        Do Until rst1.EOF
 'GETTING SYNTAX ERROR HERE
            strsql2 = "SELECT *  " & vbCrLf & _
            "FROM [NORSNAPADM_ADDRESS_NAME_LINK] WHERE [system_creation_date] in (SELECT min
(system_creation_date) & vbCrLf &" _
            "FROM [NORSNAPADM_ADDRESS_NAME_LINK] as A WHERE [BAN] = " & rst1![BAN]) & " " & vbCrLf &
            "AND [NORSNAPADM_ADDRESS_NAME_LINK] = " & rst1![BAN] & vbCrLf
   
            Set rst2 = db.OpenRecordset(strsql2, dbOpenDynaset)
       
                If rst2.BOF And rst2.EOF Then

                    'I AM ASSUMING INSERT statement will go here. I have no idea how to code it. Any help here  is greatly appreciated
           
        retVal = SysCmd(acSysCmdUpdateMeter, lineProgress)
        lineProgress = lineProgress + 1
        rst1.MoveNext
        Loop
    End If
    MsgBox ("Process Complete")
     retVal = SysCmd(acSysCmdRemoveMeter)
End Sub
frogman22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jorge PaulinoIT Pro/DeveloperCommented:
Hi frogman22,

This is not correct! You  cannot use the vbCrLf. What are you trying to do ?
0
frogman22Author Commented:
I'm using the vbCrLf to act as a carriage return so the query is not all on one long line of code
0
RyanProject Engineer, ElectricalCommented:
You don't need the VBCrLF.
Just put.

strsql2 = "SELECT *  " & _
            "FROM [NORSNAPADM_ADDRESS_NAME_LINK] WHERE [system_creation_date] in (SELECT min
(system_creation_date) & " _    ....

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SStoryCommented:
I think there is a much better way

INSERT INTO  table3 (list table3 fields here seperated by commas) SELECT (give all the values for each field in table3) FROM table1,table2 where table1.acct_nbr=table2.acct_nbr

the first set of ( ) should really be there, the ones after the select only mean, this is a comment and you need to put comma separated values from tables 1 and 2, which are the values to go into table3 (in the same order as the field listing in the INSERT statement

Doing this correctly and executing it (maybe even in access as a query) will avoid coding alltogether
0
Jorge PaulinoIT Pro/DeveloperCommented:
For me the easy way is to use a StringBuilder

Dim sb As New StringBuilder
sb.AppendFormat(" SELECT * FROM Mytable")
sb.AppendFormat(" WHERE field1 = " & Me.TextBox1.Text.ToString())
sb.AppendFormat(" ORDER BY field2")
ExecuteCommand(sb.ToString())
0
Jorge PaulinoIT Pro/DeveloperCommented:
Sorry guys this is .Net not Access :-)
0
TextReportCommented:
Dear All, lets clear up the use of the vbcrlf and & and _ This is a perfectly acceptable approach to format your SQL statement and every SQL engine I know will handle multiple line SELECTS like this below.

Now for the issues.
1. Is the table name the same as the field name
2. The syntax error is caused by the " in here & vbCrLf &" _ This is why you should lay it out as below as it is easier to spot.

Cheers, Andrew



strsql2 = "SELECT *" & vbCrLf & _
          "FROM [NORSNAPADM_ADDRESS_NAME_LINK]" & vbCrLf & _
          "WHERE [system_creation_date] in (SELECT min (system_creation_date)" & vbCrLf & _
          "                                 FROM [NORSNAPADM_ADDRESS_NAME_LINK] as A" & vbCrLf & _
          "                                 WHERE [BAN] = " & rst1![BAN] & vbCrLf & _
          "                                )" & vbCrLf & _
          "AND [NORSNAPADM_ADDRESS_NAME_LINK] = " & rst1![BAN] & vbCrLf
 
Debug.Print strSQL2
SELECT *
FROM [NORSNAPADM_ADDRESS_NAME_LINK]
WHERE [system_creation_date] in (SELECT min (system_creation_date)
                                 FROM [NORSNAPADM_ADDRESS_NAME_LINK] as A
                                 WHERE [BAN] = 1234
                                )
AND [NORSNAPADM_ADDRESS_NAME_LINK] =1234

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frogman22Author Commented:
Sstory - I have created this in a query in Acces using the query wizard. It runs fine as long as I feed it one account number at a time. It give me a "not enough memory" error when I let it loop through the 65,000 records. That is why I am coding it this way in a Module for Access 2003.  Two questions - 1. Do I need the code listed below or can I go straight to the INSERT statement as you suggest above. 2- If not is the code below correct in theory. I am getting a syntax error.  I am new at this so all help is greatly appreciated.

strsql2 = "SELECT *  " & _
            "FROM [NORSNAPADM_ADDRESS_NAME_LINK] WHERE [system_creation_date] in (SELECT min (system_creation_date) " & _
            "FROM [NORSNAPADM_ADDRESS_NAME_LINK] as A WHERE [BAN] = " & rst1![BAN])" & _
            "AND [NORSNAPADM_ADDRESS_NAME_LINK] = " & rst1![BAN]
0
frogman22Author Commented:
Andrew, Thank You very much. Now I understand the code more clearly as it works great as written. As part of the original question I now need to Insert the ban, address_iD, and min(system_creation_date) into a  table called Tbl: Individual Address_ID Info. I need the ban and address_id from rst1 and the min(system_creation_date) from NORSNAPADM_ADDRESS_NAME_LINK inserted into tha table.

again, thank you for your help
Do Until rst1.EOF
 
            strsql2 = "SELECT *" & vbCrLf & _
          "FROM [NORSNAPADM_ADDRESS_NAME_LINK]" & vbCrLf & _
          "WHERE [system_creation_date] in (SELECT min (system_creation_date)" & vbCrLf & _
          "                                 FROM [NORSNAPADM_ADDRESS_NAME_LINK] as A" & vbCrLf & _
          "                                 WHERE [BAN] = " & rst1![BAN] & vbCrLf & _
          "                                )" & vbCrLf & _
          "AND [NORSNAPADM_ADDRESS_NAME_LINK] = " & rst1![BAN] & vbCrLf
        
 Set rst2 = db.OpenRecordset(strsql2, dbOpenDynaset)
        
                If rst2.BOF And rst2.EOF Then
                    Else
                        INSERT INTO [Tbl: Individual Address_ID Info] ([BAN],[Address_ID],[MIN_SYS_CREATE_DATE]) SELECT ([BAN],[ADDRESS_ID]) FROM [Tbl: Individual Data],[NORSNAPADM_ADDRESS_NAME_LINK] where [Tbl: Individual Data].[BAN]= [NORSNAPADM_ADDRESS_NAME_LINK].[BAN]
 
                End If

Open in new window

0
TextReportCommented:
Before we go down the loop to do the insert I don't understand why you can't do it in a single insert statement. Example below but if you get the SELECT statement working you can then easily turn it into an INSERT statement.
Cheers, Andrew
INSERT INTO [Tbl: Individual Address_ID Info]
    ([BAN]
    ,[Address_ID]
    ,[MIN_SYS_CREATE_DATE]
    )
SELECT A.[BAN]
      ,[ADDRESS_ID]) 
FROM [Tbl: Individual Data] A
INNER JOIN [NORSNAPADM_ADDRESS_NAME_LINK] B
ON A.[BAN]= B.[BAN]
 
 

Open in new window

0
frogman22Author Commented:
I thought I had to do the "select" statement and get the min(system_creation_date) first before creating the INSERT statement. I am not understanding where the min(system_creation_date) is being retreived from [NORSNAPADM_ADDRESS_NAME_LINK} table in the second code sample.
0
TextReportCommented:
The best way to get to understand what is going on it to use the access queries.

1. Create a query that returns your MID dates for all records, this needs to grough by your ID
2. Create a query that returns your data and uses the query created in step 1

You should now have a query that will select all your records and will include the MIN dates you require. Now you can change this to an APPEND / INSERT query

A query like this will be more efficient that the approach you are trying to take here.

Cheers, Andrew
0
frogman22Author Commented:
Like this?
INSERT INTO [Tbl: Individual Address_ID Numbers] ( AutoNum, BAN, ADDRESS_ID, SYS_CREATION_DATE )
SELECT [Tbl: Individual Data].autonum, NORSNAPADM_ADDRESS_NAME_LINK.BAN, NORSNAPADM_ADDRESS_NAME_LINK.ADDRESS_ID, NORSNAPADM_ADDRESS_NAME_LINK.SYS_CREATION_DATE
FROM [Tbl: Individual Data] INNER JOIN NORSNAPADM_ADDRESS_NAME_LINK ON [Tbl: Individual Data].BAN = NORSNAPADM_ADDRESS_NAME_LINK.BAN;
0
TextReportCommented:
Looks promising.
0
SStoryCommented:
TextReport,

What you suggested looks a whole lot what I already told him.

frogman22, the basic idea is:

INSERT INTO takes two forms
the first is
INSERT INTO table3 (fieldname1,fieldname2,....) values('some string value','some other value', etc)
this is where we say insert a new record into table3, given the field list above, and putting values in those fields as specified in the values clause

The second way is
INSERT INTO table3 (fieldname1,fieldname2,....) SELECT SomeField,SomeOtherField,.... FROM table2

this insert one record in table3 for each row returned by the select statement.  The fields in the select statement should be the same number of fields as specified in the INSERT statement field list.

This is the best way to do it.  You can surely drop to code and loop through doing it in a cursor type fashion.... It is less efficient, but acceptable.

Also it is true that the problem with your vbcrlf is that you needed a double quote before the &

HTH,

Shane
0
JezWaltersCommented:
0
frogman22Author Commented:
Thanks for all your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.