Split sql and populate records

[Swagener]

Any responses, references and help would be really appreciated as I
can't find any material to help myself.

I will have these types of SQL text in a memo field, which will be
added through a form.
Now the challenge is this that I need to split the text starting after
SELECT and stop before FROM as well as I need to leave out the first
two letters (like a.) and need to populate records.
Then I need to pass the Logno. which will be in a int field on the
current form.

For example based on the SQL text the records will be like this with
the autonumber and Logno.


LogNo.| ID | FieldNames
02254 001 Formno
02254 002 dateraised
02254 003 raisedby


Thanks in advance.


````````````````````````SQL```````````````````````
``````````````````````````
SELECT
a.formno,
a.dateraised,
a.raisedby,
a.description,
a.priority1,
a.formstatus,
a.formstat1,
a.statdate,
a.formtype1,
a.issuedto,
a.issuedate
FROM
Adam.schangedoc a

 

[TC]

Can you give us some more background?

It is possible to do what you ask - but it's not very common.

What are you actually trying to achieve? There might be an better way.

TC (MVP Access)
http://tc2.atspace.com

 

[Swagener]

Hi Tim,

The problem is that i,m trying to design a Risk Management tool for
Business Intelligence in our organization.
People will go on to this database and via form will enter the
information about their complex data extractions routines, reports,
scheduled queries, executive alerts and etc. etc.
Becuase its gonna be too time consuming for them to select fields they
used in their work, the best way i decided is that they copy,paste the
sql
and the application populates fields for further information.

Hope this Helps

 

[TC]

Sorry, I'm not sure if I understand you.

Where does the initial SQL text come from? Are you expecting the
end-users to create and enter it?

It's quite late here, so I am off to bed in a moment. I'll see your
answer when I get up tomorrow.

Cheers
TC (MVP Access)
http://tc2.atspace.com

 

[Swagener]

They will be created in another software and then copy it from there
and paste it in access memo field on a form.

Hope this clarifies.

 

[Swagener]

Just read and found out that i,m making no sense.
Sorry for the confusion,
Let me give ya bit more detail and background as it got really
complicated at least for me.
First there is a bound form with only two fields from tbl_Main
LogNO which is a AutoNumber
SqlText which is a Memo Field

Users add a New Record and paste the SQL text from their clipboard
which they
copied from a third party software into
the Forms!FRMBISQL!Sql

After that I,m planning to have a cmd button which holds the code to
prase the SQL in the Memo Field.
And based on the parsed data after SELECT and before FROM,
I would like to add records in the tblFields which has three fields
So after every carriage return in the memo text it needs to create a
new record.

FieldName (FieldTypes)
FieldsNo (AutoNumber)
LogNo (text)
Field (text)

The LogNo will be passed from the form current record updated.

Hope this makes sense and thanks for the reply.

 

[Swagener]

Can any body help me further