Solved

Need Help,, i m beginner in vba,, i'm writing a code for exporting the data from Userform to Access Database

Posted on 2016-09-15
13
93 Views
Last Modified: 2016-10-05
i'm writing a code for transfer the data from Userform to Access Database,,please ...can any1 suggest me for coding for multi user to transfer the  data from userform to access data.....
0
Comment
Question by:Salman Khan
  • 7
  • 6
13 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
Comment Utility
Firstly, I think we need to clarify something. Is this code in an application that is not Access itself (hence UserForm) or is in Access, which doesn't have Userforms, because Access forms do the job?
1
 

Assisted Solution

by:Salman Khan
Salman Khan earned 0 total points
Comment Utility
hi,, thanks for the quick response,..i m created userform in Excel, and transfer userform data from excel userform to Access database,,, below are the code :-

Dim con As Object
Dim rs As Object

Set con = CreateObject("Adodb.connection")
con.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Mode=Share Deny None;Jet OLEDB:Database Locking Mode=1;Data Source=\\mumchfs04\Serco Data\INFO Tool CRM\Backup\Old Folder\New folder (3)\CRM Data\Disposition_Report.accdb;Jet OLEDB:Database Password=didididi;")
If con.State = adStateClosed Then con.Open
If Me.Disposition.Value = "Call Back" Or Me.Disposition.Value = "Expected" Then

so many textbox values etc.

Set rs = con.Execute("insert into disposition_report(Company_Code, Disposition, Sub_Disposition, Comment, Called_By, Called_Number, Spoken_to, Callback_Date, Email_ID, New_Number1, New_Number2, Designation, New_EmailID ,InfoEmail1, InfoEmail2, InfoEmail3, InfoEmail4, Document1, Document2, Document3, Document4, Document5,Document6,RAG,Saved_Time)" + _
        "Values('" & CompanyCode & "','" & Disposition & "','" & SubDisposition & "','" & Comment1 & "','" & SavedBy & "','" & Callednumber & "','" & SpokenTo & "','" & Callbackd & "','" & EmailId & "','" & NewNum1 & "','" & NewNum2 & "','" & Desig & "','" & newemail & "','" & Infmail1 & "','" & Infmail2 & "','" & Infmail3 & "','" & Infmail4 & "','" & Doc1 & "','" & Doc2 & "','" & Doc3 & "','" & Doc4 & "','" & Doc5 & "','" & Doc6 & "','" & rag & "','" & SavedAt & "')")
If con.State = adStateOpen Then con.Close
Set rs = Nothing
Set con = Nothing
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
Comment Utility
Where have you placed your code and how is it called?

You will need some way of opening and filling the Userform. Is that already done?

A couple of points.

If you are a beginner, you might find it easier to use early binding. For this you will need to set references -  via Tools/References in the VBA IDE  - to libraries for any objects not already included. Excel will already have references to VBA, Excel itself, OLE, Office and Forms 2. You are using ADO, so the reference should be to the latest version of the
Microsoft ActiveX Data Objects 6.0 Library.

With early binding you get F1 context help on referenced objects. You will also be able to view and search the library members via the F2 key. 'Intellisense' will be active as well.

Early binding defines built-in constants like adStateClosed.

If you have Option Explicit (and you should) at the top of your code modules you will get an error: Variable not defined, If you don't, such numeric constants will be treated as 0.

I note your example shows the habit of setting object variables to Nothing before leaving the  procedure. This is quite unnecessary, since the objects will be destroyed automatically at the end of the procedure.
1
 

Assisted Solution

by:Salman Khan
Salman Khan earned 0 total points
Comment Utility
Thanks for all the tips..it work for me..but my major problem is:
1) I have 15 user at time to access the main database, they call the customer and tagged status on excel Userform >>from the excel userform its get import the data to access.accdb file; but sometimes its damaged the access file or locked.>> then i need to open the file  its show this error(Snap1) i just need a vba code  or solution to avoid this error.

Also i attached my dump (excel uefrom)
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
Comment Utility
You haven't successfully attached anything. It is a three-stage process:

Attach File
Choose File
Upload File
1
 

Assisted Solution

by:Salman Khan
Salman Khan earned 0 total points
Comment Utility
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
Comment Utility
Your .png file shows a message that starts:
"Microsoft Access has detected that this database in in an inconsistent start, and will attempt to recover the database." It goes on to say that it will make a backup copy and recover what it can , putting the recovered objects in the new file. It will report unrecovered objects in the "Recovery Errors" table.

Was the creation of the new database successful and is there anything in the "Recovery Errors" table?

The .xlsm file reports a "Compile error in hidden module: Module1" when it is opened. The code is password protected, so I can't diagnose that problem.
0
 

Assisted Solution

by:Salman Khan
Salman Khan earned 0 total points
Comment Utility
Was the creation of the new database successful >>>>>Yes we created the data and works good, but sometime we are getting this error.

and is there anything in the "Recovery Errors" table?>>> no we dont found anything in recovery table

The code is password protected, so I can't diagnose that problem.>>>>>>>I'm Extremely Sorry Password is =.?
dot and question mark
0
 

Assisted Solution

by:Salman Khan
Salman Khan earned 0 total points
Comment Utility
Hi Awaiting for reply, please help me
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
Comment Utility
Sorry. I found problems with the syntax of the API calls and then got involved with something else.
I am looking at it again right now.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
Comment Utility
The problem I had with the API syntax was the PtrSafe keyword. It is only needed for 64 bit versions, and mine is 32 bit.

I cannot see any reason for the database inconsistency errors. The code seems to simply add a new row to a table.

It might be a problem with the drive that the database is on, so it could be worth trying on another drive and/or another server.

You have multiple users. This is sometimes problematic with Access databases, so it might be worth using transactions. I don't believe that your simple use needs it, but here is  link to Microsoft's MSDN entry in case you want to try it:
https://msdn.microsoft.com/en-us/library/ms680895(v=vs.85).aspx
0
 

Author Closing Comment

by:Salman Khan
Comment Utility
thanks for all your assistant i appreciate your all feedback and quick response,,,,one more thing i want to know,,,is there any way to compact and repair data base using ADO ?
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
No but you can use  the ADO extension: Microsoft Jet OLE DB Provider and Replication Objects (JRO).

Details are in this Microsoft article:
https://support.microsoft.com/en-us/kb/230501
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This is about my first experience with programming Arduino.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now