[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Access 2003 VBA Coding Question

Posted on 2008-02-05
18
Medium Priority
?
277 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:frogman22
  • 6
  • 4
  • 3
  • +3
18 Comments
 
LVL 48

Expert Comment

by:Jorge Paulino
ID: 20827180
Hi frogman22,

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

Author Comment

by:frogman22
ID: 20827230
I'm using the vbCrLf to act as a carriage return so the query is not all on one long line of code
0
 
LVL 13

Expert Comment

by:Ryan
ID: 20827293
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 25

Expert Comment

by:SStory
ID: 20827296
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
 
LVL 48

Expert Comment

by:Jorge Paulino
ID: 20827372
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
 
LVL 48

Expert Comment

by:Jorge Paulino
ID: 20827381
Sorry guys this is .Net not Access :-)
0
 
LVL 28

Accepted Solution

by:
TextReport earned 2000 total points
ID: 20827500
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
 

Author Comment

by:frogman22
ID: 20827589
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
 

Author Comment

by:frogman22
ID: 20828158
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
 
LVL 28

Expert Comment

by:TextReport
ID: 20828258
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
 

Author Comment

by:frogman22
ID: 20828309
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
 
LVL 28

Expert Comment

by:TextReport
ID: 20828400
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
 

Author Comment

by:frogman22
ID: 20828654
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
 
LVL 28

Expert Comment

by:TextReport
ID: 20828680
Looks promising.
0
 
LVL 25

Expert Comment

by:SStory
ID: 20831790
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 20833974
0
 

Author Closing Comment

by:frogman22
ID: 31428282
Thanks for all your help.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question