[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
Medium Priority
?
151 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
[X]
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
  • 7
  • 6
13 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 2000 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 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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 2000 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 2000 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Introduction to Processes

650 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