By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,278 Members | 1,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,278 IT Pros & Developers. It's quick & easy.

Visual Basic 6.0 to MS Access to MS Excel

DUNXALEARE
P: 21
Hello everyone!
I have a new assignment. I just dont know how to create a data report using ms excell.

I need to search particular group of data/records using Visual basic 6. in MS Access and transfered this selected data in MS Excel. to generate a report.

Please can anyone give me a tip or I'd rather say the syntax on how to edit,delete,add,search data in ms access using visual basic 6 without using Adodc component. (you see im having problem in using adodc component and directly connecting the textbox on adodc's data field the computer prompt me operation cancelled).

And proper way of transferring records/data from MS Access using VB to MS Excel.

thans Folks ;)
Mar 29 '07 #1
Share this Question
Share on Google+
11 Replies


Dököll
Expert 100+
P: 2,364
Greetings, DUNXALEARE!

I am sure it's probable and perhaps there are many ways to start.

(1) How much time are you willing to invest taking on this project?
(2) Do you have anything working at this point?

Please excuse my passiveness. It'd be helpful to you if we know what you have thus far.

You may need to get one thing working then tackle the next (i.e. VB to Access first, then Excel). It's also likely, a great tutorial will be of use if you're interested and have the time to look through.

Welcome!

Dököll
Mar 29 '07 #2

DUNXALEARE
P: 21
Greetings, DUNXALEARE!

I am sure it's probable and perhaps there are many ways to start.

(1) How much time are you willing to invest taking on this project?
(2) Do you have anything working at this point?

Please excuse my passiveness. It'd be helpful to you if we know what you have thus far.

You may need to get one thing working then tackle the next (i.e. VB to Access first, then Excel). It's also likely, a great tutorial will be of use if you're interested and have the time to look through.

Welcome!

Dököll

As of now i try to connect and manipulate the data in ms access using vb 6. but i encountered some problems like run time error because i used directly an adodc components ( the only way i know to transfer or retrieve data in access) . I guess i need to focus on connecting visual basic to ms access using other method other than adodc components, before i proceed to ms excel, is that possible?
Im just a begginner so i know only basic ,but I will be glad if I'll learn advance or new programming techniques from you guys.

Kind regards :)
Apr 1 '07 #3

DUNXALEARE
P: 21
Expand|Select|Wrap|Line Numbers
  1.  
  2. saves_click()
  3. Select Case act
  4.   Case 2
  5.     Select Case cond
  6.       Case 0
  7.         ansr = MsgBox("The employee you enter already exist. Would you like the record to display?", vbYesNo, Label)
  8.           If ansr = vbYes Then
  9.             act = 0
  10.             ans = txt_no(0).Text
  11.             epis.Recordset.CancelUpdate
  12.             educ.Recordset.CancelUpdate
  13.             family.Recordset.CancelUpdate
  14.             flag = False
  15.             saves.Enabled = flag
  16.             initialization
  17.             search
  18.           Else
  19.             epis.Recordset.CancelUpdate
  20.             educ.Recordset.CancelUpdate
  21.             family.Recordset.CancelUpdate
  22.               Exit Sub
  23.           End If
  24.       Case 1
  25.         ans = MsgBox("Are you sure you want to save this record?", vbYesNo, Label)
  26.           If ans = vbYes Then
  27.             educ.Recordset.MoveFirst
  28.             family.Recordset.MoveFirst
  29.             epis.Recordset.MoveFirst
  30.               MsgBox "You succesfully save the data", vbOKOnly, Label
  31.               txt_no(0).Visible = True
  32.           Else
  33.             GoTo 4
  34.           End If
  35.       Case Else
  36.         'act = 2 and cond <> 0 or 1
  37.     End Select
  38.   Case 3
  39.     If cond = 1 Then
  40.       MsgBox "The record's identification code has been modified. System failed to update.", vbCritical, Label
  41.       epis.Recordset.CancelUpdate
  42.       educ.Recordset.CancelUpdate
  43.       family.Recordset.CancelUpdate:
  44.       epis.Recordset.MoveFirst
  45.       educ.Recordset.MoveFirst
  46.       family.Recordset.MoveFirst
  47.     Else
  48.       'act = 3 and cond <> 1
  49.     End If
  50.   Case Else
  51.     'act <> 2 or 3
  52. End Select
  53.  
  54. act = Empty
  55. acts = Empty
  56. Form_Load
  57.  
  58. 4:
  59. End Sub
  60.  
That is one part of my coding and it focus on saving. it runs smoothly 10 to 20 times, until i encounter a runtime error "operation cancelled"
Apr 1 '07 #4

Dököll
Expert 100+
P: 2,364
As of now i try to connect and manipulate the data in ms access using vb 6. but i encountered some problems like run time error because i used directly an adodc components ( the only way i know to transfer or retrieve data in access) . I guess i need to focus on connecting visual basic to ms access using other method other than adodc components, before i proceed to ms excel, is that possible?
Im just a begginner so i know only basic ,but I will be glad if I'll learn advance or new programming techniques from you guys.

Kind regards :)
All is probable and the possibility relies on your patience. You will get it. You can achieve as you please D...

Perhaps adodc is what you should tackle first, with minimal coding.

Try searching this forum using "DataReport Adodc1". You'll find a number of posts on this. Continue with Excel afterwards. How's that for a deal?
Apr 2 '07 #5

Dököll
Expert 100+
P: 2,364
Expand|Select|Wrap|Line Numbers
  1.  
  2. saves_click()
  3. Select Case act
  4.   Case 2
  5.     Select Case cond
  6.       Case 0
  7.         ansr = MsgBox("The employee you enter already exist. Would you like the record to display?", vbYesNo, Label)
  8.           If ansr = vbYes Then
  9.             act = 0
  10.             ans = txt_no(0).Text
  11.             epis.Recordset.CancelUpdate
  12.             educ.Recordset.CancelUpdate
  13.             family.Recordset.CancelUpdate
  14.             flag = False
  15.             saves.Enabled = flag
  16.             initialization
  17.             search
  18.           Else
  19.             epis.Recordset.CancelUpdate
  20.             educ.Recordset.CancelUpdate
  21.             family.Recordset.CancelUpdate
  22.               Exit Sub
  23.           End If
  24.       Case 1
  25.         ans = MsgBox("Are you sure you want to save this record?", vbYesNo, Label)
  26.           If ans = vbYes Then
  27.             educ.Recordset.MoveFirst
  28.             family.Recordset.MoveFirst
  29.             epis.Recordset.MoveFirst
  30.               MsgBox "You succesfully save the data", vbOKOnly, Label
  31.               txt_no(0).Visible = True
  32.           Else
  33.             GoTo 4
  34.           End If
  35.       Case Else
  36.         'act = 2 and cond <> 0 or 1
  37.     End Select
  38.   Case 3
  39.     If cond = 1 Then
  40.       MsgBox "The record's identification code has been modified. System failed to update.", vbCritical, Label
  41.       epis.Recordset.CancelUpdate
  42.       educ.Recordset.CancelUpdate
  43.       family.Recordset.CancelUpdate:
  44.       epis.Recordset.MoveFirst
  45.       educ.Recordset.MoveFirst
  46.       family.Recordset.MoveFirst
  47.     Else
  48.       'act = 3 and cond <> 1
  49.     End If
  50.   Case Else
  51.     'act <> 2 or 3
  52. End Select
  53.  
  54. act = Empty
  55. acts = Empty
  56. Form_Load
  57.  
  58. 4:
  59. End Sub
  60.  
That is one part of my coding and it focus on saving. it runs smoothly 10 to 20 times, until i encounter a runtime error "operation cancelled"
Nice code, keep it handy for later usage. Get VB to Access working first. See one of my posts on DataReport? Please search, you'll save yourself some effort...
Apr 2 '07 #6

DUNXALEARE
P: 21
Nice code, keep it handy for later usage. Get VB to Access working first. See one of my posts on DataReport? Please search, you'll save yourself some effort...
THANKS FOLKS :)
I'll definitely look for that report
Apr 3 '07 #7

DUNXALEARE
P: 21
All is probable and the possibility relies on your patience. You will get it. You can achieve as you please D...

Perhaps adodc is what you should tackle first, with minimal coding.

Try searching this forum using "DataReport Adodc1". You'll find a number of posts on this. Continue with Excel afterwards. How's that for a deal?

heyo i've searched your post about connectiong visual basic to ms access.
your forum with "Yoda". I guess you still remember him.
the splash and login is working but in "about" I'm Stucked.

i folow your coding there and i've got the same error Yoda was saying
" Compile Error -Undefined User-Defined Type not Defined"
I follow the whole discussion between you and yoda about it.

did you found solution about that error.
kind regards

DUNXALEARE :)
Apr 4 '07 #8

Dököll
Expert 100+
P: 2,364
heyo i've searched your post about connectiong visual basic to ms access.
your forum with "Yoda". I guess you still remember him.
the splash and login is working but in "about" I'm Stucked.

i folow your coding there and i've got the same error Yoda was saying
" Compile Error -Undefined User-Defined Type not Defined"
I follow the whole discussion between you and yoda about it.

did you found solution about that error.
kind regards

DUNXALEARE :)
A solution was indeed, found, and Yes Yoda's a good man. A gentle person of our group searched and found a solution to the error and posted a link for the Scripts Forum. Incidentally, the code works on my machine, deals with the access version, it seems...

Please look at last few posts, 11th, or 12th position of that discussion. Let me kow how it turns out :-)

Still, I think you need DataReport, as Yoda's had to do with communicating with Access...
Apr 6 '07 #9

Dököll
Expert 100+
P: 2,364
I'll fetch for Adodc1 DataReport, and will push it forward for all to see, remember to keep an eye on Adodc1 D, ok...
Apr 6 '07 #10

DUNXALEARE
P: 21
I'll fetch for Adodc1 DataReport, and will push it forward for all to see, remember to keep an eye on Adodc1 D, ok...
sure hope to see it very soon. :)
Apr 11 '07 #11

Dököll
Expert 100+
P: 2,364
sure hope to see it very soon. :)
Good, hopefully user-friendly:-)
Apr 11 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.