Get info from Access in Visual Basic

  • Thread starter Thread starter Kevin Legg
  • Start date Start date
K

Kevin Legg

I have imported raw data from a source outside of MS
Access. After querying and making tables, I have got the
raw information into a nice neat package.
The table name is "Duplicate SO table" . SO = Sales Order
Which includes the following fields:
SO number . Text
Part Number .. Text
Qty Ordered ..Integer
Customer Name .. Text
Customer PO .. Text

I want to take the value in "Qty Ordered" and copy the
record "Qty ordered" times and append the associated
record to a table called "Serial# master table"

I am assuming that I will need to use VB or a series of
SQL statements to accomplish the above.

I have programmed before in "C++". If I can get specific
field info into VB can run with it from there. However, I
do not know the syntax or procedures for getting the
information out of Access. I searched the help files and
have not been able to ascertain how to accomplish my
task.

I have edited SQL queries, but I am unable to generate a
functional program from scratch.

In addition to solving my immediate problem, can anyone
recommend a book for writing VB programs specifically
with MS Access?

Thanks,
KPL
 
I am always suspicious when data needs to be "repeated". Repeating data
usually hints of a bad, or non normalized design.

Also, try and avoid spaces in your object names.


However,t he code shell to do what you want would look like. I do assume you
added a autonumber field called id to uniquely identify the record.

dim strUpdate as string
dim rstRecs as dao.RecordSet
dim intQty as integer
dim i as integer

set rstRecs = currentdb.OpenRecordSet("Duplicate SO table")

do while rstRecs.EOF = false

intQty = rstRecs![Qty Ordered]
strUpdate = "INSERT INTO CopyTable SELECT * from [Duplicate So table] " &
_
"where id = " & restRecs!ID
for i = 1 to intQty
currentdb.Execute strUpdate
next i
rstRecs.MoveNext
loop
rstRecs.Close
set rstRecs = nothing

The above is air code, and written as is without checking..but the flow
looks quite close. Also, you can see what pain those table names with spaces
in the are. Don't use spaces for table names, fields etc. Most commercial
sql databases don't allow spaces for names in objects (ms-access is a
exception). Further, since you developed in c++, I sure you see how bad
spaces are for object names, and fact could never use spaces for var names
etc anyway...
 
Back
Top