Question regarding VBA, ASPs, .dlls and Access

[bgreenhouse]

Hi everyone

I have a really simple module written in VBA as a module in Access. The module just searches for every date in the database and updates it by a set number of months. Here is the code:

Option Compare Database
Option Explicit

Sub Main()

Dim DB As Database, TB As TableDef, FD As Field, SQL As String

Set DB = CurrentDb
For Each TB In DB.TableDefs
If TB.Name <> &quot;CognosData&quot; And TB.Name <> &quot;CustomerCodedInformation&quot; And TB.Name <> &quot;Logins&quot; And TB.Name <> &quot;TimePeriod&quot; And Left$(TB.Name, 4) <> &quot;MSys&quot; Then
For Each FD In TB.Fields
If FD.Type = dbDate Then
If FD.Name <> &quot;DateAmended&quot; Then
If FD.Name <> &quot;DateOnFile&quot; Then
SQL$ = &quot;UPDATE &quot; & TB.Name
'This is where you change the date...right now it's 15 days added
SQL$ = SQL$ & &quot; SET &quot; & FD.Name & &quot; = DateAdd('d', 15, &quot; & FD.Name & &quot&quot;
SQL$ = SQL$ & &quot;;&quot;
DB.Execute SQL$
End If
End If
End If
Next
End If
Next
DB.Close
End Sub

I'd like to do two things...I'd like to be able to run this off of the web, and I'd like to be able to input the number of days or months to advance it from the web page, probably from a form. I haven't seen anyone explaining how to access modules from the web, so I'm presuming I'll have to change this into a COM object...I don't know VBA or VB too well, certainly I'm more familiar with VBA.

ANy advice, starting points, or info would be greatly appreciated.

 

[woyler]

You would need to create a ActiveX dll and put your code there as a public function/sub. In an ASP page you could use VBScript to access the dll, and pass the date entered from the page to the dll.

 

[bgreenhouse]

Thanks! Would the code need to be much different than the VBA I've posted?

Ben

 

[woyler]

I would suggest ADO. Just to get you started:

Public Function ModifyDate(byval psDate as String) as Boolean

Dim cnConn As Connection
Dim sSQL As String

On error goto ERROR:

Set cnConn = CreateObject(&quot;ADODB.Connection&quot

cnConn.Connectionstring = &quot;Driver={Microsoft Access Driver (*.mdb)};Dbq=YourDatbasePathHere;Uid=Admin;Pwd=;&quot;
cnConn.open
** As far as what you are doing in your code, it looks like **you know what Table and Field you are looking for. If **that is the case then:

'teke the date passed in from the web page and add 15 days
'to it
psDate = DateAdd('d', 15, cdate(psDate))
'Set the SQL statement
sSQL = &quot;UPDATE TABLE SET FIELD ='&quot; & psDate & &quot;'&quot;
cnConn.Execute sSQL

ModifyDate = true
if not cnConn is Nothing Then Set cnConn = Nothing

Exit Function
ERROR:
err.Raise err.number,err.description,err.sourcce
ModifyDate = false
end function

 

[bgreenhouse]

Thanks alot! That looks good, one more question (or perhaps more...). It doesn't look like vb recognizes TableDef as a variable type (it's surely specific to Access VBA...)Is there anyway to import this data type, or this function (basically, I'm scanning all tables - with the exception of a few specific ones - for the date date type, then changing them...). Is there anyway I can import the data types (I guess they're objects?) from Access?

Thanks for all your help!

Ben

 

[woyler]

To be perfectly honest, I do not know. Maybe someone else in this forum can help out on that. One way around that for now would be to have a table that contains two fields: One that would contain the tables you want to update and secondly, the field in the related table that holds the date. This way, instead of looping through every Table/Field every time, you can just use the table_list table.

 

[rtshort]

I will be facing the same thing shortly (hopefully) and would like to know also. woyler, do you know if it can be compiled as a &quot;dhtml&quot; program and run as a web app. I was told it could be done but I'm new to all of this myself. Every day above ground is a GOOD DAY!!!

 

[woyler]

I am not exactly sure what you mean. You can use HTML or DHTML for the purpose of displaying your web page. The interface between your ASP and the dll will require some type of script. The thread I listed is somewhat related to this. maybe it will clarify things slightly.

thread222-114576