• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

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

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
Salman Khan
Asked:
Salman Khan
  • 7
  • 6
11 Solutions
 
GrahamSkanCommented:
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
 
Salman KhanAuthor Commented:
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
 
GrahamSkanCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Salman KhanAuthor Commented:
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
 
GrahamSkanCommented:
You haven't successfully attached anything. It is a three-stage process:

Attach File
Choose File
Upload File
1
 
Salman KhanAuthor Commented:
0
 
GrahamSkanCommented:
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
 
Salman KhanAuthor Commented:
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
 
Salman KhanAuthor Commented:
Hi Awaiting for reply, please help me
0
 
GrahamSkanCommented:
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
 
GrahamSkanCommented:
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
 
Salman KhanAuthor Commented:
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
 
GrahamSkanCommented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now