|
I am trying to import tables and selected fields from an ODBC Source to Access. Can anyone help me with the code that automatically imports selected tables and fields from the external database. The connection to the database is thru ODBC. Does this Help?
|
|
|
|
|
There is nothing that will "automatically" do this. You must write the code to reads one database and posts the data to another. What code have you written so far?
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave:
what I meant by automate is doing it via VB Code. I dont have any code yet. I know there are 2 phases to this.
#1 to be able to connect to the external databse. (need code for this)
#2 to be able to read and import tables into access. (need code for this)
Scenario:
In External Database Import to Access
Table1 ----> Export table1 (selected fields)
Table2
Table3 ----> Export to Access (selected fileds)
Table4
|
|
|
|
|
Is there anything you don't need code for? You just described needing code for the entire application. Not gonna happen here. You have to try it yourself first.
If your importing into Access, why not just use the "Get External Data" wizard of the "Table Link" wizard. It will let you do everything you've described so far.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave:
I am trying to write a application. The part I dont understand is how to automatically pull in data from external source. This is not the full application, but it is the main component of extracting data into the system. I cannot use the wizard process because I need the data extraction part to be processed when the application is started. I know how to connect to the source, but dont know how to instruct the code to only import few tables and selected fields out of the table. All I am looking for is some generic code help and I can go from there. If you don't want to share any thoughts, could you at least point me to a resource site or something where I can get basic concept on getting this code going. Many Thanks.
|
|
|
|
|
This part requires a little SQL to select the tables and fields you want returned in a recordset. The Web, and Access' documentation, is filled with examples of how to do this. (I assume your writing this in VBA for Access.) I don't have any code examples, in VBA, to show you because I haven't used Access for anything in years. Just look in the Access documentation for the terms Connection, Command and Recordset and you'll find all the objects you need to read and write a database and examples to go with it.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave:
I really appreciate your help this far. This is what I have got so far.
Private Sub ImportToAccess()
Dim Con1 As New ADODB.Connection
Dim Con2 As New ADODB.Connection
Dim mySQL1 As String
Dim mySQL2 As String
Dim myDSN As String
Dim mySET As String
myDSN = "DSN=Springbrook1;UID=suresh;PWD=******;"
mySET = "set schema 'pub'"
mySQL2 = "select * from customer"
Con1.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Temp\VB Sample Codes\mcwd\Connect Program\test1.mdb;" & _
"Jet OLEDB:Engine Type=5;"
Con2.Open myDSN
Con2.Execute (mySET)
mySQL1 = "SELECT * INTO [tblCutomer] FROM [mySQL2]"
Con1.Execute mySQL1
Set Con1 = Nothing
Set Con2 = Nothing
Con2.Close
End Sub
I am getting the following error message:
The Microsoft Jet databse engine cannot find the input table or query 'mySQL2'. Make sure it exists and that its name is spelled correctly.
Is my code correct? Am I missing something for the mySQL2 line. If this works, I am sure I can define selected fields to be imported. Thanks for your help.
|
|
|
|
|
Suresh Prasad wrote:
The Microsoft Jet databse engine cannot find the input table or query 'mySQL2'. Make sure it exists and that its name is spelled correctly.
I think something problem with your string. In the code mySQL2 is the SQL statement that declare above and your system try to look in the database as the table. It look like this because write this code:
Suresh Prasad wrote:
mySQL1 = "SELECT * INTO [tblCutomer] FROM [mySQL2]"
look at the bold text that you write, your system will look into your database for table mySQL2. I'm not sure what you want to do with the above SELECT statement but what i guest is you try to insert the data that you want to import in your system from the result of the data that you select. As far as I know, if the structure of add the new record in datase is INSERT INTO not SELECT INTO... (not sure too). But I just suggest you that try to use loop (but it could take some time to do that) to read the record one by one from the source then insert it in your database. This is the concept only try to do it by yourself and let me know if you face any problem.
Suresh Prasad wrote:
mySQL1 = "SELECT * INTO [tblCutomer] FROM [mySQL2]"
Con1.Execute mySQL1
Set Con1 = Nothing
Set Con2 = Nothing
Con2.Close
End Sub
On the other hand, your code above did not close the Con1 and Con2 connection object. I think a good practice you should close it first then set it to nothing.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Can anyone tell me whats wrong with my code. I am getting a syntax error on the INSERT staement. Also, will this work to import the data into access.
Private Sub ImportToAccess()
Dim Con1 As New ADODB.Connection
Dim Con2 As New ADODB.Connection
Dim mySQL1 As String
Dim mySQL2 As String
Dim myDSN As String
Dim mySET As String
'Connection parameters for Source Database
myDSN = "DSN=Springbrook1;UID=suresh;PWD=****;"
mySET = "set schema 'pub'"
mySQL2 = "select * from customer"
'Open Source Database
Con2.Open myDSN
Con2.Execute (mySET)
'Open Destination Database
Con1.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Temp\VB Sample Codes\mcwd\Connect Program\test1.mdb;" & _
"Jet OLEDB:Engine Type=5;"
'Read from Source Database
mySQL1 = "SELECT Cust_No, First_Name, Last_Name FROM [odbc;DSN=Springbrook1;UID=suresh;PWD=nissan;].[Customer]"
'Import Data to Destination Database
mySQL2 = "INSERT INTO [C:\Temp\VB Sample Codes\mcwd\Connect Program\test1.mdb].[tblCustomer] "
Con1.Execute mySQL1
Con1.Execute mySQL2
Con1.Close
Con2.Close
Set Con1 = Nothing
Set Con2 = Nothing
End Sub
|
|
|
|
|
First, you can't use a SELECT statement to transfer data between two physically seperate databases.
Second, your only opening one database. You haven't setup anything to open the second one where your going to transfer data to.
Third, you have to retrieve the records from your first database, and one at a time, do any processing you need to on the data, and use an INSERT sql statement/command object to insert the data into the new database.
The pseudo-code looks something like this:
' Open source database connection
' Open destination database connection
' Retrieve all the records we need from the source database
' For each record in source recordset
' Assign values from record fields to parameters in SQL INSERT command,
doing any translation or processing required.
' Execute INSERT command
' Next
' Close destination database connection
' Close source database connection
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
|
This is an English-speaking board...
And I'm not clicking on a link going to Russia...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
hi, i want to make a program by vb6.0 which i can get the emails by it and i can connect the mail server by it, hope any 1 can help me 
Thanks alot
Metal Man
|
|
|
|
|
You're going to have to be much more specific about what you're having a problem with. So far, all you've done is ask a 'yes' or 'no' question...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Try to check the information from here.[^]
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
How can i use VS.net Setup to pack MSDE with my application and install MSDE if it's not installed.
|
|
|
|
|
You really can't do that, unless you found a good merge module for MSDE.
Search CodeProject and other sites, like GotDotNet.com, for "bootstrap setup". You'll find many projects and articles that deal with exactly this problem.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
How can I add GUID in my application. I m going to develop a messenger, I want that when the user is sign in his/her account, then window display message for his/her signing in and open his/her login of messenger.
|
|
|
|
|
iamalik wrote:
How can I add GUID in my application.
You can generate a new GUID using the shared NewGuid method in the GUID structure:
Dim MyAppId as GUID = GUID.NewGuid
Hope this helps
Tom
|
|
|
|
|
Need help. Drivers often want to know the miles per gallon their cars get so they can estimate gasoline costs. I need to develop an application that allows the user to input the number of miles driven and the number of gallons used for a tank of gas. First thing is to calculate the miles per gallon. Write a function procedure MilesPerGallon that takes the number of miles driven and gallons used (entered by user), calculates the amount of miles per gallon and returns the miles per gallon for a thankful of gas.
Displaying the results. I have to create a click event handler for the Calculate MPG button that invokes the Function procedure MilesPerGallon and displays the result returned from the procedures
Need Help!!!!!!!
|
|
|
|
|
Put 2 textboxes, 3 labels and a button on a form:
Label1:
Text = "Miles driven:"
Location = 32, 24
Label2:
Text = "Gallons used:"
Location = 32, 48
Label3:
Text = ""
Location = 144, 80
TextBox1:
Text = ""
Location = 144, 24
TextBox2:
Text = ""
Location = 144, 48
Button1:
Text = "Calculate"
Location = 232, 112
Add the following code
<br />
Private Function MilesPerGallon(ByVal miles As Integer, ByVal gallons As Integer) As Double<br />
<br />
Return miles / gallons<br />
<br />
End Function<br />
<br />
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br />
<br />
If IsNumeric(TextBox1.Text) And IsNumeric(TextBox2.Text) Then<br />
Label3.Text = GetMPG(CInt(TextBox1.Text), CInt(TextBox2.Text)).ToString & " mpg"<br />
End If<br />
<br />
End Sub<br />
Then you may also want to ensure the user can only enter numbers into the textboxes:
<br />
Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress, TextBox2.KeyPress<br />
<br />
If Not IsNumeric(e.KeyChar) Then e.Handled = True<br />
<br />
End Sub<br />
Hope this helps
Tom
|
|
|
|
|
Great! Another homework problem completely written for the student. I'll tell you right now, that he'll get a failing grade. Not because the code is wrong or the result is incorrect, but because (1) it's not his code, and (2) you haven't taught him anything.
Everyone has a certain coding style that is like a figerprint. If a student deviates from the style, compared to previous code he's submitted, he'll get nailed and failed. Stealing someone elses code is a capital offense in these classes.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
mmm.... i did not see the other post until after i replied... figured it was a fleet manager or something wanting a quick app for his drivers... Anyway, if he's got any sense he'll look at the code provided and understand it before presenting it in his own way.
|
|
|
|
|
Tom John wrote:
figured it was a fleet manager or something wanting a quick app for his drivers
Tutors and Lecturers have a distinct style for detailing coursework that no one else on the planet uses. See below, the first sentence is in the imperative. In the second paragraph why does he "have to" create a click event handler, surely that's common knowledge (Chapter 1 material in many books on creating Windows Forms applications) and one doesn't need to be told they "have to" create it.
Write a function procedure MilesPerGallon that takes the number of miles driven and gallons used (entered by user), calculates the amount of miles per gallon and returns the miles per gallon for a thankful of gas.
Displaying the results. I have to create a click event handler for the Calculate MPG button that invokes the Function procedure MilesPerGallon and displays the result returned from the procedures
Anyway, at least you now know what to look for in a homework question. 
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hi All,
Everytime I try to run below code [Dim newTN As TreeNode = CType(tn.Clone(), TreeNode)], I receive run-time error:
No parameterless constructor defined for this object.
[CODE]
Private Sub RecurseNodesForFiltering(ByVal Node As TreeNodeCollection)
Try
Dim reproj As Regex
reproj = New Regex(UCase(txtFiltering.Text))
For Each tn As TreeNode In Node
'remove unsearched nodes
If reproj.IsMatch(UCase(tn.FullPath)) Then
Dim newTN As TreeNode = CType(tn.Clone(), TreeNode)
newTN.Parent.Remove()
gobjDispTV.SelectedNode.Nodes.Add(newTN)
End If
RecurseNodesForFiltering(tn.Nodes)
Next tn
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.Critical)
End Try
End Sub
[/CODE]
Any idea how to solve this problem ?
Thank you very much for any helps and suggestions.
Regards.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
