Transfer data from a MsAccess db to another via ODBC connection
Hi Experts,
I'm trying to figure how I can transfer data from a MsAccess database to another MsAccess database. I wanted to use VB and insert a SQL command to insert in the table the data and update the other data.
How I can do this?
Thanks
stronghold888
I'm trying to figure how I can transfer data from a MsAccess database to another MsAccess database. I wanted to use VB and insert a SQL command to insert in the table the data and update the other data.
How I can do this?
Thanks
stronghold888
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm not that new to programming. It's just been a long time I stopped to program, that's why without practice, my SQL knowledge went down.
This will help. I don't know if it will work because most fields in the second database are different from the first. I will do this then come back if I encouter some problems.
Thanks
stronghold888
This will help. I don't know if it will work because most fields in the second database are different from the first. I will do this then come back if I encouter some problems.
Thanks
stronghold888
ASKER
It did not worked. I made another attempt, so I linked the primary table from the database 1 to the second table. I think then it would be better to run a query, so I made this, but still don't work :(
INSERT INTO TblStatements(StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
SELECT (StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
FROM Src_TblCalendar;
That was the INSERT, but need also to update and there I am completely lost
I was thinking about this:
SELECT SELECT (StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
FROM Src_TblCalendar;
while NOT EOF
UPDATE TblStatements SET (StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
where PK = Same PK;
end loop
The structure in the first database is very different of the one in the other database even if most fields are the same.
stronghold888
INSERT INTO TblStatements(StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
SELECT (StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
FROM Src_TblCalendar;
That was the INSERT, but need also to update and there I am completely lost
I was thinking about this:
SELECT SELECT (StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
FROM Src_TblCalendar;
while NOT EOF
UPDATE TblStatements SET (StatID, ActivityID, Calendar_E, Calendar_F, Directorate, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, CheckLink, CheckEms, CheckReq, SMEID, Comments, Rev_Stat)
where PK = Same PK;
end loop
The structure in the first database is very different of the one in the other database even if most fields are the same.
stronghold888
save it in an xml file.
ASKER
do you mean save the query in an xml file?
not xml file, sorry got you confused...
put it in a subroutine.
put it in a subroutine.
ASKER
hmm...something like this:
sub Update()
dim sSQL as string
sSQL = "UPDATE <THE NAME OF YOUR TABLE> SET <THE NAME OF YOUR TABLE>.<NAME OF THE FIELD THAT YOU WANT TO MODIFIED>= <VALUE>
WHERE (((<THE NAME OF YOUR TABLE>.<NAME OF THE FIELD THAT YOU WANT TO MODIFIED>)=<VALUE FILTER>))"
codedb.execute sSQL
end sub
If you refer to this, I'm not that sure it will really work :(
sub Update()
dim sSQL as string
sSQL = "UPDATE <THE NAME OF YOUR TABLE> SET <THE NAME OF YOUR TABLE>.<NAME OF THE FIELD THAT YOU WANT TO MODIFIED>= <VALUE>
WHERE (((<THE NAME OF YOUR TABLE>.<NAME OF THE FIELD THAT YOU WANT TO MODIFIED>)=<VALUE FILTER>))"
codedb.execute sSQL
end sub
If you refer to this, I'm not that sure it will really work :(
ASKER
This code need to be inserted in a module or just somewhere else?
Thanks
stronghold888