looking for vb comands 2

[FacilitiesCAD]

Hi everyone, I'm fairly newat using access but I'm getting there. I am trying a project to import data into an access database. I have a group of 1700 files (AutoCAD drawings) in a number of sub directories that I'm trying to collect data from. I am mostly there.

The database currently has the file name, date & time saved, file path. The next thing to do is collect some data from the file itself.

I want to press 1 button and get it to do the following things (Some I can do and some I can't)

Can 1) Open Autocad LT
can 2) Open a specific file (whichever entry I'm
looking at)
Can 3) Run a batch routine to extract data
(This all happens together)
Can't 4) Empty a table "Access_Acad" (so it will only have
certain entries later)
Can't 5) Wait for 20 seconds (or basically until the data
is sent to a csv file.
Can 6) Import the data into "Access_Acad" (without single
quotes)
Can't 7) run query "Access_Acad_Query" to get specific data
from the imported data in step 6
Can't 8) Take information from the query and set it in
my "Input_Data" table ie [me] [Input_Data] [Note] =
[Access_Acad_Query] [TITLE1] and [Title2] and [title3]

If [Access_Acad_Query]Dwgnum= "" then [Input_Data]
[dwg_Name]=[Access_Acad_Query]Dwgno else
[Input_Data][dwg_Name]=[Access_Acad_Query]Dwgnum

etc
basically in step 4 I dont know how to do a delete command
in step 5 I dont know how to wait in step 7 i dont know how to run a query and in step 8 I dont know how to specify the specific query point because its not just me.anything.

Any help is apreciated.

Tim

 

[Rolliee]

Tim,

It is so easy to do this with VB, I would just do it. Let me show you the code. Some have said on this forum that this is too complicated but I believe not and I have the job done while they are talking about it.

private sub command0_click()

dim rs as DAO.recordset
set rs = currentdb.openrecordset{"MyTable&quot

do while not rs.eof
rs.delete
rs.movenext
loop
rs.close
set rs = nothing
exit sub


Be sure the Tools/ References MS DAO 3.6 box is clicked


rollie @bwsys.net

P. S. The time can be programmed to wait 20 secs just as easily.

 

[JeremyNYC]

4) Why would you step through a recordset instead of just deleting all the records at once with a sql statement?
currentdb.Execute("delete * from YourTable&quot

will do that part.

5) Here's code I got off the web about five years ago. I don't know where Trevor's at these days, so I'll take the liberty of posting his code. He's a great coder, and taught me much. This will allow you to make your code wait for any specified amount of time:
Sub WaitFor(psngSeconds As Long)
' wait for specified number of seconds
' Copyright Trevor Best (trevor@besty.org.uk)

Dim sngStart As Single
Dim sngET As Single

sngStart = Timer
DoEvents
Do While sngET < psngSeconds
DoEvents
' check for midnight as the Timer() function will
' rollover at this point
sngET = Timer - sngStart
If sngET < 0 Then
' it rolled over - add number of seconds
' in a day
sngET = sngET + 86400
Beep
End If
' now don't hog the processor
' release some CPU slices back to Windoze
DoEvents
Loop
End Sub

6) You'll have to give MUCH more information. I'd do this in a separate post.

7) Running a query is quite simple (currentdb.QueryDefs(&quot;YourQuery&quot.Execute). Is there more to this question you're not showing us? Or is it just that you can't do it because you can't do step 6?

8) Look up help on Append queries and iif statements (note the two Is).

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[FacilitiesCAD]

Thanks all,
I can't look at this problem for a day or two now but I'll let you all know how it turns out.

Tim

 

[FacilitiesCAD]

Jeremy, your commands have helped alot. I'm pretty close to getting this to work. Bellow is the code I'm using. I ended up using a message box to wait for Autocad to finish its thing. All I need to do now is take the data from the query and put it into the current database line. I'm still not following how to address it. In step 8 I want to:

iff(query.Drawnum = nil ,me.Drawing_Number = query.drawno,me.Drawing_Number = query.drawno)

set

me.title =[Access_Acad_Query]![TITLE1] & &quot; &quot; & [Access_Acad_Query]![TITLE2] & &quot; &quot; & [Access_Acad_Query]![TITLE3]

and then do a message box or sub form or something to choose what rev and date equal

me.rev =[Access_Acad_Query]![rev] or =[Access_Acad_Query]![rev1] etc [rev8]


Any sugestion on how to improve the code are apreciated too.

Tim


Private Sub Command47_Click()
On Error GoTo Err_Command47_Click


Dim stAppName As String
Dim stFileName As String
Dim stBatch As String
Dim quote As String
Dim ID As Integer
' Dim Acces_Acad As TableDef
ID = Me.ID 'just in case I need it later
stBatch = &quot; /b S:\cd-eng\access.scr&quot;
quote = &quot;&quot;&quot;&quot;
stAppName = &quot;P:\AutoCadLT_2000\aclt.exe&quot;
stFileName = Me.Path & &quot;\&quot; & Me.File_Name
'' Call Shell(stAppName & &quot; /b S:\cd-eng\access.scr&quot;, 1)
'''' Steps 1,2,3
Call Shell(stAppName & &quot; &quot; & quote & stFileName & quote & stBatch, 1)
'' Call Shell(stAppName, 1)
'''

'''' Step 4
CurrentDb.Execute (&quot;delete * from Access_Acad&quot
'
'''' Step 5
Dim PauseTime, Start, Finish, TotalTime

If (MsgBox(&quot;Press Yes or No when Autocad is done&quot;, 4)) = vbYes Then

Else

End If


'''' Step 6
' Thanks to JeremyNYC
' DoCmd.TransferText
DoCmd.TransferText acImportDelim, &quot;Access_Acad_TH&quot;, &quot;Access_Acad&quot;, &quot;S:\cd-eng\1Cad files\Database of drawings\Access_Acad.txt&quot;

'''' Step 7
' Thanks to JeremyNYC
' CurrentDb.QueryDefs(&quot;Access_Acad_Query&quot.Execute 'didn't work

Dim stDocName As String

stDocName = &quot;Access_Acad_Query&quot;
DoCmd.OpenQuery stDocName, acNormal, acEdit


'''' Step 8 Still learning
' Thanks to JeremyNYC

Exit_Command47_Click:
Exit Sub

Err_Command47_Click:
MsgBox Err.Description
Resume Exit_Command47_Click

End Sub