VBA CODE - HELP

[Neera]

Hi,
I need to code an application where there is an access table. This table will be updated based on several conditions. To update the table, a txt file will be supplied. Please help me to code:
1. OPen the database in VBA
2. Open the table and read the records.
3. Compare the data with the txt file and update the table.
4. In the process of update, the record in the access table can be replaced, updated, or new record is added.
Thanks for your help.

 

[Chance1234]

if you are doing this in Access then .....easy way...

create a Macro that imports the text into a new table or adds to an exisitng table (have a run sql opion and type DELETE * From [table name] to empty it out first

then create some action querys fot the actions above, add them into the macro to run

create a form
stick a button on the form
assign the macro you have just written to the button

go tools convert all macros to VBA

and theres your code

if you are running it from another app, you will need to reference microsfot access and reference the database

 

[Neera]

Thanks for the tip. I really need to code the application in Visual Basic. By usign the query, it is just coding the DoCommand.OpenQuery and runs the query. It is not giving me the code to open the database, read the records, compare and then update. Please let me know if you can guide me for that code.
Thanks

 

[Chance1234]

text files i always have trouble with readuing from VB but for the rest of it masybe..


Dim Mydb as Database
Dim StrSQL(3) as string
dim x

set mydb = opendatabase("C:\temp\database.mdb&quot

strsql(1) = "UPDATE * FROM [mytable1] WHERE [number] = 1"
strsql(2) = "DELETE * FROM [mytable2]
strsql(3) = "INSERT INTO [mytable2] SELECT [mytable1.numbers] FROM [mytable 1]


For x = 1 to 3

mydb.execute strsql(x)

next

 

[Dzidze]

Do the following:

In VB, create a project.
Go to project,references and click:
Microsoft DAO 2.5-3.5 Compatibility Library
Create a Module, and insert the following coding:


Global gWk As Workspace
Global gDb As Database
Global gRs As Recordset

Function OpenDatabase() As Boolean

On Error GoTo err_opendatabase

Set gWk = CreateWorkspace("", "admin", "", dbUseJet)
Set gDb = gWk.OpenDatabase("C:\...\x.mdb", , False)
OpenDatabase = True

Exit Function

err_opendatabase:
OpenDatabase = False
MsgBox Error(Err)
End
End Function

Function OpenRecordset() As Boolean
Dim sSQL As String

On Error GoTo err_openrecordset

sSQL = "SELECT tblX.x_id, tblx.x_name, "
sSQL = sSQL & "tblx.date_arrived "sSQL
sSQL = sSQL & "FROM tblX "

Set gRs = gDb.OpenRecordset(sSQL, dbOpenSnapshot)
OpenRecordset = True

Exit Function
err_openrecordset:
OpenRecordset = False
MsgBox Error(Err)
End
End Function

Sub main()

If OpenDatabase() = True Then
If OpenRecordset() = True Then
Form1.Show vbModal
End If
End If

End Sub


Make sure to set this new project properties so it starts with main.


Since this 3 variables are global, you can use them through out your project.


Have fun!!!

 

[Dzidze]

I found a mistake in my example:
Here is the right code for the function:

Function OpenRecordset() As Boolean
Dim sSQL As String

On Error GoTo err_openrecordset

sSQL = "SELECT tblX.x_id, tblx.x_name, "
sSQL = sSQL & "tblx.date_arrived "
sSQL = sSQL & "FROM tblX "

Set gRs = gDb.OpenRecordset(sSQL, dbOpenSnapshot)
OpenRecordset = True

Exit Function
err_openrecordset:
OpenRecordset = False
MsgBox Error(Err)
End
End Function