Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
120 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
ID: 41800144
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
ID: 41800160
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
ID: 41801247
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Assisted Solution

by:Salman Khan
Salman Khan earned 0 total points
ID: 41812224
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
ID: 41813768
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
ID: 41816168
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
ID: 41816721
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
ID: 41817183
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
ID: 41828395
Hi Awaiting for reply, please help me
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
ID: 41828623
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
ID: 41828809
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
ID: 41828923
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
ID: 41829746
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

829 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