asked on

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

Jorge Paulino

Hi frogman22,

This is not correct! You cannot use the vbCrLf. What are you trying to do ?

frogman22

ASKER

I'm using the vbCrLf to act as a carriage return so the query is not all on one long line of code

Ryan

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) & " _ ....

SStory

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

SStory

some samples:

http://en.allexperts.com/q/Using-MS-Access-1440/Insert-procedure.htm

https://www.experts-exchange.com/questions/20848896/INSERT-INTO-query.html

Jorge Paulino

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())

Jorge Paulino

Sorry guys this is .Net not Access :-)

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

frogman22

ASKER

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]

frogman22

ASKER

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

TextReport

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

frogman22

ASKER

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.

TextReport

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

frogman22

ASKER

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;

TextReport

Looks promising.

SStory

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

Jez Walters

This looks a bit familiar!

https://www.experts-exchange.com/questions/23138044/VBA-INSERT-Statement-Get-MAX-Date-Time.html

frogman22

ASKER

Thanks for all your help.