Art Exhibitions            Index           Links           Home             Feedback

Last                                                                   Next

Access Database Tutorials Community Tute 28 - Advanced - Scripting

1. Start Access and open the database rdrobotics.mdb, select the Modules panel. click here to download.

2. Open the BasicScriptModule, and choose tools > references > References and check that DAO 3.6 Object Library is selected.




3. Run the module, note this writes a file to the following path: c:\insert.sql

4. Here is the code used to generate the sql


Public Function getData()

Dim MyDB As Database, MyREC As Recordset, criteria1 As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Open "C:\gen.sql" For Output As intOutFile
Dim nFile1 As Integer
Dim Dname As String
'retrieve the drawing name
Dname = ""
'get the next free file number
nFile1 = 1
'open the file to append to
Open "c:\insert.sql" For Append As #nFile1

Set rst = MyDB.OpenRecordset("rdlinks_local", dbOpenTable)

Dim insertsql As String
With rst
Do While Not rst.EOF

For Each fld In .Fields
If Not fld.Name = "id" Then

If fld.Name = "hits" Or fld.Name = "rank" Or fld.Name = "user_id" Or fld.Name = "parent_id" Then
If IsNull(fld.Value) Then
insertsql = insertsql & "," & "0"
Else
insertsql = insertsql & "," & fld.Value
End If
Else
If IsNull(fld.Value) Then
insertsql = insertsql & "," & "''"
Else
insertsql = insertsql & ",'" & fld.Value & "'"
End If
End If

End If

Next fld
insertsql = "INSERT INTO rd_links(user_id,parent_id,name,name_long,description,link_url,hits,rank) Values(" & insertsql
insertsql = insertsql & ");"
'Debug.Print insertsql
'write to the file
Print #nFile1, insertsql & Dname

insertsql = ""
'.Close
.MoveNext
Loop
End With


'close the file
Close #nFile1


End Function





Copyright © 1999 R&D Robotics Pty Ltd, All rights reserved.
Any copyright subsisting in the above code and or literary works including source files vests with R&D Robotics Pty Ltd. ACN 086 345 137

Last                                                                   Next