How run a module in access DB automatically

[fabdulla]

I have Database A and Database B. No I have written a vba code to copy a tabel from database A into Database B. But I need this to run every day. I don't want to go and open the database the run it. Is there an easier way of accomplishing this task. If so please help.:WAVE:

[Hack]

Did you write this in Access VBA?

[fabdulla]

Yes I did write the module in access VBA

[Hack]

As far as I know the only way to run Access VBA code it from Access itself, so you would have to open up the database and run your macro.

However, can you take the code out of Access VBA and put it into something like VB6 or VB.NET which can be compiled into an executable and perhaps set up to run, via a scheduler program, at a certain time each day?

[fabdulla]

let say i take it and run it from a .net framework then how would recongnize the access database. Do i have to open the databse connection. I don't know much about this. please if you know advise.

[Hack]

Connecting to any type of database with VB.NET is pretty simple. You just need the right connection string.

From there, you can take what you have in your macro and probably translate it into a COPY TABLE query.

Have you done any type of database work with .NET?

[fabdulla]

no I have not. Can point me to the right direction how to go about doing that.

[Hack]

Well, the first thing you need to do is get connected to your Access Database. This should work for you in that regardImports System.Data.OleDb

Public Class Form1

Private connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\fullpath\yourmdb.mdb;"
Private conn As OleDb.OleDbConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
conn = New OleDbConnection(connstring)
conn.Open()
End Sub
End Class

[fabdulla]

that works thanks

[Hack]

Good.

Do you know what to do in terms of translating your VBA code into something that can work in VB.NET?

[fabdulla]

No idea

[Hack]

Well, post your VBA code and perhaps we can come up with something.

[Amahdy]

I have Database A and Database B. No I have written a vba code to copy a tabel from database A into Database B. But I need this to run every day. I don't want to go and open the database the run it. Is there an easier way of accomplishing this task. If so please help.:WAVE:

http://www.google.com/search?q=auto+run+macro+in+access+vba

the first result has a good discussion about what you want to do exactly execpt he want to autosend and you want an auto copy, good luck !

[Hack]

Well, post your VBA code and perhaps we can come up with something.Lets see what he is doing here and how easily it can be translated into .NET code.

[Amahdy]

I don't think another coding is a good idea, he is using access which enable interactive macros; and finally we will use a schedular so why do that throught another program that connect to access db and all that, just make the macro be scheduled I think this is better...

[Hack]

Running a macro requires the database be open does it not?

How can a scheduler do that?

[Amahdy]

discussed in google results , the link i sent before :)
BTW: a macro can run "like" an executable file when the access is installed on the system.

[fabdulla]

Is it possible to schedule a macro. if so how.

[Amahdy]

my last post in the "first" page .

[Hack]

Actually, your link is two posts before the last post on the first page.

fabdulla, look at the first page, Post #13

[fabdulla]

Ok i have the following code in VB.NET, i have that when it runs it grabs data from access DB table into another table with access db. The only problem I am getting is when I move the .exe to the server it gives me an error.

"log file write failed: request for the permission of type system.security.permission.fileIOpermission, mscorlib, version=2.0.0.0, culture=neutral,... "

Here is the code i am using.
#Region "import"
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Imports System.Security
Imports System.Security.Permissions
Imports System.Security.Policy
Imports System.Collections


#End Region

Public Class Form1
Inherits System.Windows.Forms.Form
'Dim fileName As String = "C:\Documents and Settings\u00322\Desktop\error.txt"

Dim fileName As String = "\\ps-mon-dcs\d$\Backup Data\bos archives\unit 1\db12errorLog.txt"

#Region "load fuction this function connects to access DB and copy data from one table to another"
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
Me.Opacity = 0
Me.ShowInTaskbar = False

Try
Dim acc As New OleDbConnection
acc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ps-mon-dcs\d$\Backup Data\bos archives\unit 1\db12.mdb"
acc.Open()
Dim i As Integer
For i = 1 To i = 4

Dim cm2 As New OleDbCommand("Delete from test(i)", acc)
cm2.ExecuteNonQuery()

Dim Cm As New OleDbCommand(" Insert into test(i) select * from BlrBypass(i)", acc)
Cm.ExecuteNonQuery()

Next

acc.Close()
Catch ex As Exception
ErrorLogWrite(ex.Message.ToString)
End Try
End Sub
#End Region

#Region " (ErrorLogWrite) This function writes to the Error log file to document the programs problems"
Sub ErrorLogWrite(ByVal message As String)
message = message & " at " & Format(Now(), "dd/mm/yy")
Try
Dim sw As StreamWriter = File.AppendText(fileName)

sw.WriteLine(message)
sw.Close()
Catch ex As Exception

MsgBox("Log file write failed : " & ex.Message)
End Try
End Sub
#End Region

End Class

[Hack]

I added your code goes here tags to your post to make reading the posted code easier.

My immediate question is do you have permissions to create a file in the location on the server that it is trying to write to?

[fabdulla]

i got this error when i debug around the second "Cm.ExecuteNonQuery()"

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Disk or network error.

[Hack]

What is Test(i) and BlrBypass(i)?

[fabdulla]

The file already exists in the server, it just when have an error i should write into that file, how would I figure out if i have permission or not.

[Hack]

The file already exists in the server, it just when have an error i should write into that file, how would I figure out if i have permission or not.Go out to the server and see if you can create a text file.

If you can't, then you don't have write permissions.

[fabdulla]

I have 4 tabls (test1,test2,test3,test4)and tables(Blrbypass1,...,BlrBypass4) so did a loop that would just go and delete existing data in testx and get updated data from BlrBypassx

[fabdulla]

I did create the file in the server

[Hack]

I have 4 tabls (test1,test2,test3,test4)and tables(Blrbypass1,...,BlrBypass4) so did a loop that would just go and delete existing data in testx and get updated data from BlrBypassxWell, you can't do that. You need to explicity use the table names. You can't treat them as an array.

[fabdulla]

If i used the following code it runs the first ExecuteNonQuery, but when i reaches the second it skips it and everything blow.
sql = "Delete from test1"
cmd = New OleDbCommand(sql, acc)
cmd.ExecuteNonQuery()
sql = "Insert into test1 select * from BlrBypass1"
cmd = New OleDbCommand(sql, acc)
cmd.ExecuteNonQuery()

[Hack]

Are test1 and BlrBypass1 completely and totally identical in field names, types and overall table structure?

If they are not, then you need to specifiy what fields will be involved in your INSERT/SELECT

[fabdulla]

yes,

[Hack]

What happens if you run the INSERT by itself? Does it work? (If everything is identical, then it should be working.)

[fabdulla]

what do you mean run the insert by its self. can please explain

[fabdulla]

I ran just the insert and nothing happend as nothing in test1 table.

[fabdulla]

Actually there is one column that has different datatype. in the test table there is a column [Date] of datetime datatype where as in BlrBypass table the column is of Text datatype and I connot change the one in BlrBypass becasue the entire database gets replaced every 2 hours. That is why I did it this way so I can have all the data in the proper type. But would the datatype make not work

[Hack]

Try is with specifying the fields. Example:sql = "INSERT INTO test1 (fieldname1, fieldname2, fieldname3) SELECT fieldname1, fieldname2, fieldname3 FROM BlrBypass1

[Hack]

But would the datatype make not workYes

[fabdulla]

is there a way that we can copy but some how change the data type

[Hack]

I think if you specify the fields involved in the INSERT and SELECT, you will be fine.

[fabdulla]

I specified the fields and still nothing works. There is not data in the test table.

[fabdulla]

I checked the log file and i see the following message:
The database has been placed in a state by user 'Admin' on machine 'PS-MON-DCS' that prevents it from being opened or locked. at 12/4/2007 12:30:55 PM

[fabdulla]

I received the following message.
Syntax error in INSERT INTO statement. at 12/4/2007 12:34:06 PM
the statement is as follows:
sql = "Insert into test1(INDEX, BLOWER, JOB NUMBER, DATE, TIME, TAG DESCRIPTION) select (INDEX, BLOWER, JOB NUMBER, DATE, TIME, TAG DESCRIPTION) from BlrBypass1"
cmd = New OleDbCommand(sql, acc)

acc.Open()
cmd.ExecuteNonQuery()

[Hack]

There are two possible reasons for the syntax error:

1. There should not be any parentheses around the field names in the SELECT part.
2. Both DATE and TIME are reserved words and should NEVER be used as field names. If you are stuck with them, then when you use them in a query, put them in brackets. Try thissql = "Insert into test1(INDEX, BLOWER, JOB NUMBER, [DATE], [TIME], TAG DESCRIPTION) "
sql = sql & "select INDEX, BLOWER, JOB NUMBER, [DATE], [TIME], TAG DESCRIPTION "
sql = sql & "from BlrBypass1 "
cmd = New OleDbCommand(sql, acc)
acc.Open()
cmd.ExecuteNonQuery()

[Hack]

I just saw something else that I didn't notice the first time. Spaces in field names should be avoided as well. Use underscores. Since you do have a space in a field name, that needs to go in brackets as well.sql = "Insert into test1(INDEX, BLOWER, [JOB NUMBER], [DATE], [TIME], TAG DESCRIPTION) "
sql = sql & "select INDEX, BLOWER, [JOB NUMBER], [DATE], [TIME], TAG DESCRIPTION "
sql = sql & "from BlrBypass1 "
cmd = New OleDbCommand(sql, acc)
acc.Open()
cmd.ExecuteNonQuery()

[fabdulla]

I have the following and i still get the same error: "Syntax error in INSERT INTO statement. at 12/5/2007 10:03:19 AM"
how about the two word columns " JOB NUMBER " we leave them as they are.
sql = "Insert into test1(INDEX, BLOWER, JOB NUMBER, [DATE], [TIME], TAG DESCRIPTION) "
sql = sql & "select INDEX, BLOWER, JOB NUMBER, [DATE], [TIME], TAG DESCRIPTION "
sql = sql & "from BlrBypass1 "
cmd = New OleDbCommand(sql, acc)

[Hack]

See Post #45 on Page 3 of this thread.

JOB NUMBER has to be in brackets as well.

[Hack]

Did you try the revised query?

[fabdulla]

still gives me an error that there is an error with the insert into statement

[fabdulla]

I tried testing the just inserting values into the test table
I used the following

sql = "Insert into test1([INDEX], [BLOWER], [JOB NUMBER], [DATE], [TIME], [TAG DESCRIPTION])values('1', 'dk', 'fkj','01/02/07','12:43', 'dfd')"

it only worked when i used ' ' around xx==>'xx' it worked so I tried to apply it here

sql = "Insert into test1([INDEX], [BLOWER], [JOB NUMBER], [DATE], [TIME], [TAG DESCRIPTION])"
sql = sql & " select 'INDEX', 'BLOWER', '[JOB NUMBER]', '[DATE]', '[TIME]', '[TAG DESCRIPTION]'"
sql = sql & " from BlrBypass1 "
cmd = New OleDbCommand(sql, acc)
acc.Open()
cmd.ExecuteNonQuery()


i got this message in the log file
Disk or network error. at 12/5/2007 3:29:40 PM

[fabdulla]

never mind I actually got it to work. Thanks Alot for your help.

[Hack]

What did you do? It might help someone else.

Thanks. :)

[fabdulla]

That what I had at the end, but i was running it from my laptop and the files are in a server. it was giving an error "Disk or network error. " than I ran the .exe from the server and worked fine.

sql = "Insert into test1([INDEX], [BLOWER], [JOB NUMBER], [DATE], [TIME], [TAG DESCRIPTION])"
sql = sql & " select INDEX, BLOWER, [JOB NUMBER], [DATE], [TIME], [TAG DESCRIPTION]"
sql = sql & " from BlrBypass1 "
cmd = New OleDbCommand(sql, acc)
cmd.ExecuteNonQuery()

[Hack]

Thank you for posting your solution. So many times people just come back and say something like "Never mind I figured it out" without sharing what they came up with.

Hopefully, this will help not only you, but others that might have the same, or similiar, issue. :)

[fabdulla]

I have another question, when the program is running using the task scheduler how do i make the form no show. basically run everything, but not to show a form pop in the window screen.
i used

Me.Opacity = 0
Me.ShowInTaskbar = False

but it only wroks when i run it on my laptop from the vb.net run comand , but when i hit the .exe it pop up a form to show.

[bschaettle]

I believe that there is a command line switch to tell Access to run a macro when it opens. The macro in turn can call a VBA routine. The last command in the VBA code can be DoCmd.Quit which will close the database.

[Hack]

I believe that there is a command line switch to tell Access to run a macro when it opens. The macro in turn can call a VBA routine. The last command in the VBA code can be DoCmd.Quit which will close the database.I'm not sure I understand how this relates to his last question. Perhaps a bit more detail.