Append data from one database to another

  • Thread starter Thread starter TIML
  • Start date Start date
T

TIML

I am going to have a database open 3 times a day. The only function of this
database is to move data from one database to another. I need to write some
code that opens the connections and then closes them. We used to have a
link to the datasource in access, but it is corrupting the program access is
running when someone has the link open. So I need to run some code to
insert the data into a holding table, so that we can use that table without
any complications.

I tried this:

Sub mytest()

Dim sql As String


sql = "INSERT INTO EMPLOYEES ( [EMPLOYEE ID], [CLOCK NAME], [CLOCK ID],
[FULL NAME], ADDR1, ADDR2, PHONE, SSN, SORT1, SORT2, SORT3, STATUS, CLASS,
[PAYROLL ID], MESSAGE, MSGCODE, FULLTIME, SCHEDULE, OLDSCHEDULE, NEWBADGE,
SwipeAndGo, PicturePath, AccessControl ) IN
'\\george\public\databases\accounting\secure\time.mdb' SELECT
EMPLOYEES.[EMPLOYEE ID], EMPLOYEES.[CLOCK NAME], EMPLOYEES.[CLOCK ID],
EMPLOYEES.[FULL NAME], EMPLOYEES.ADDR1, EMPLOYEES.ADDR2, EMPLOYEES.PHONE,
EMPLOYEES.SSN, EMPLOYEES.SORT1, EMPLOYEES.SORT2, EMPLOYEES.SORT3,
EMPLOYEES.STATUS, EMPLOYEES.CLASS, EMPLOYEES.[PAYROLL ID],
EMPLOYEES.MESSAGE, EMPLOYEES.MSGCODE, EMPLOYEES.FULLTIME,
EMPLOYEES.SCHEDULE, EMPLOYEES.OLDSCHEDULE, EMPLOYEES.NEWBADGE,
EMPLOYEES.SwipeAndGo, EMPLOYEES.PicturePath, EMPLOYEES.AccessControl FROM
EMPLOYEES"

DoCmd.RunSQL sql

End Sub


and it works, but i need the same kind of thing that lets you choose another
database where the FROM EMPLOYEES is.
 
Maybe I'm missing something obvious, but can't you just use another IN
clause, e.g.

FROM Employees IN '\\server\share\folder\source.mdb'

Also, if the only thing you're trying to do is move data from a table in
one .mdb file to a table in another, there's no need to create another
database and open it 3 times a day. You can do it with a VBScript script
that uses the DAO library to execute an SQL statement against a
database, and is itself run as a scheduled task on the computer.
Something like:

'Sample VBScript to execute a SQL statement

Option Explicit

Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String


strSQL = "blah blah blah"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("\\server\share\folder\source.mdb")

oDB.Execute strSQL

oDB.Close

I am going to have a database open 3 times a day. The only function of this
database is to move data from one database to another. I need to write some
code that opens the connections and then closes them. We used to have a
link to the datasource in access, but it is corrupting the program access is
running when someone has the link open. So I need to run some code to
insert the data into a holding table, so that we can use that table without
any complications.

I tried this:

Sub mytest()

Dim sql As String


sql = "INSERT INTO EMPLOYEES ( [EMPLOYEE ID], [CLOCK NAME], [CLOCK ID],
[FULL NAME], ADDR1, ADDR2, PHONE, SSN, SORT1, SORT2, SORT3, STATUS, CLASS,
[PAYROLL ID], MESSAGE, MSGCODE, FULLTIME, SCHEDULE, OLDSCHEDULE, NEWBADGE,
SwipeAndGo, PicturePath, AccessControl ) IN
'\\george\public\databases\accounting\secure\time.mdb' SELECT
EMPLOYEES.[EMPLOYEE ID], EMPLOYEES.[CLOCK NAME], EMPLOYEES.[CLOCK ID],
EMPLOYEES.[FULL NAME], EMPLOYEES.ADDR1, EMPLOYEES.ADDR2, EMPLOYEES.PHONE,
EMPLOYEES.SSN, EMPLOYEES.SORT1, EMPLOYEES.SORT2, EMPLOYEES.SORT3,
EMPLOYEES.STATUS, EMPLOYEES.CLASS, EMPLOYEES.[PAYROLL ID],
EMPLOYEES.MESSAGE, EMPLOYEES.MSGCODE, EMPLOYEES.FULLTIME,
EMPLOYEES.SCHEDULE, EMPLOYEES.OLDSCHEDULE, EMPLOYEES.NEWBADGE,
EMPLOYEES.SwipeAndGo, EMPLOYEES.PicturePath, EMPLOYEES.AccessControl FROM
EMPLOYEES"

DoCmd.RunSQL sql

End Sub


and it works, but i need the same kind of thing that lets you choose another
database where the FROM EMPLOYEES is.
Click to expand...
 
Back
Top