[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transfer data from a MsAccess db to another via ODBC connection

Posted on 2007-10-10
9
Medium Priority
?
188 Views
Last Modified: 2010-03-20
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
0
Comment
Question by:stronghold888
  • 5
  • 3
9 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 900 total points
ID: 20048767
example:
Dim statement As String
Dim conn As ADODB.Connection

    ' db_file is the Access database's file name.
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_file & ";" & _
        "Persist Security Info=False"
    conn.Open

    ' Compose the INSERT statement.
    statement = "INSERT INTO Addresses " & _
        "(Name, Street, City, State, Zip) " & _
        " VALUES (" & _
        "'" & txtName.Text & "', " & _
        "'" & txtStreet.Text & "', " & _
        "'" & txtCity.Text & "', " & _
        "'" & txtState.Text & "', " & _
        "'" & txtZip.Text & "'" & _
        ")"

    ' Execute the statement.
    conn.Execute statement, , adCmdText

    ' Close the connection.
    conn.Close
0
 
LVL 4

Author Comment

by:stronghold888
ID: 20048867
Hi Sql_Server Dba,

This code need to be inserted in a module or just somewhere else?

Thanks

stronghold888
0
 
LVL 17

Assisted Solution

by:Shanmuga Sundaram
Shanmuga Sundaram earned 600 total points
ID: 20049354
I think you are a new to programming. If I am right then do as follows.

Open a new project.
Click on the Projects menu, Select References option,
Ensure that Microsoft Data Access Active x 2.0 library is checked.
Click on the OK button.

In the form add a command button.

Copy the above code in the command_click event.

Hope this would help you
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:stronghold888
ID: 20050185
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
0
 
LVL 4

Author Comment

by:stronghold888
ID: 20058447
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
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20058530
save it in an xml file.
0
 
LVL 4

Author Comment

by:stronghold888
ID: 20058543
do you mean save the query in an xml file?
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20058573
not xml file, sorry got you confused...

put it in a subroutine.
0
 
LVL 4

Author Comment

by:stronghold888
ID: 20058637
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 :(
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question