Error Insert Data Into Access DB

A Website That Will Teach Me How To Insert Data Into An MS Access File, Access...
A website that will teach me how to insert data into an MS Access file, access the data (Read into vb file) edit it.

I would like to learn the different functions of MS Access and how to integrate it with VB.

I would like to know if there is a website that outlines this all in a nice package.

Thank You!

---

How To Insert Temporary Data In Access(table) Using Data Grid
How to insert data into a table using a data grid.
A little background,i am using SS TAB in my forms i need to hold data temporarly im my data grid
and then finaly at click it should be inserted in the table.The SS TAB is linked to ADOC,which is inturn
linked to the table in access.

---

How To Insert Temporary Data In Access(table) Using Data Grid
How to insert data into a table using a data grid.
A little background,i am using SS TAB in my forms i need to hold data temporarly im my data grid
and then finaly at click it should be inserted in the table.The SS TAB is linked to ADOC,which is inturn
linked to table.



Edited by - manav74 on 10/29/2003 1:01:07 AM

---

Insert Data Into MS Access 97
I want to insert value into ms access 97, how to do it? wat reference have to be added? my table name is LOGFILE pls help

---

Access SQL Insert Error
hi,

can anyone help with this please.

i'm trying to execute an INSERT INTO SQL command in my vb.net code, but i keep getting the error message "syntax error in INSERT INTO command". I'm running my app on windows server2000 at the moment, but i dont think that would cause a problem. I've tried executing the command on an access97 and 2000 version of my database. i've tried making my table names in the database as simple as possible and even reduced the number of columns in my database to just one for testing purposes and i still get the error message. i'm certain there's nothing wrong with the syntax - any ideas?

here's my code.

Dim sConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Data Source=" & Application.StartupPath & "Datadb.mdb;Mode=Share Deny None;Persist Security Info=True"
objConn = New System.Data.OleDb.OleDbConnection(sConnection)

Dim sSQL As String

sSQL = "insert into user (IDNO) values (657)"


Dim objCmd As New System.Data.OleDb.OleDbCommand(sSQL, objConn)
Try
'opens and executes the prepared query
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
Catch ex As System.Exception
MsgBox(ex.Message)
Finally
If objConn.State = ConnectionState.Open Then
objConn.Close()
End If
End Try


the code crashes in the try statement when it executes the objcmd.executeNonQuery() statement. I know that its definitely opening the connection properly, as far as i can tell i've narrowed it down to the contents of the sSQL string variable being the problem. if its not this then it must be an external thing that i haven't set up correctly on the windows or dtabase system. do i need to do something in the ODBC links screen, in the Administrative tools icon in the control panel? all i've done externally to vb.net is install ms access 2000 and created a simple database for testing this command out before doing the rest of my code.

i've tried searching for help on other threads on here, but they're syntax looks the same as mine so i'm clueless.

many thanks for any help

---

MS Access INSERT INTO Error
Hello all. Having some trouble with some VB with MS Access and inserting a row into a table. The insert command works fine with one column only, but trying to insert 2 or more columns into the record fails with: Syntax Error in INSERT INTO Statement. Any help would be greatly appreciated.


Code:
Dim conBadge As ADODB.Connection

Set conBadge = New ADODB.Connection
conBadge.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NCOAM2004.mdb"
conBadge.Open

conBadge.Execute ("INSERT INTO Badge(Rank, First) VALUES('" & txtRank & "', '" & txtFirst & "')")

conBadge.Close
Joe Nudi III

---

Access DB Insert Error
When i try to insert data with any of the characters used by VB6 such as..

&
*
%
"

I get an error because it is trying to parse this data. is there an easy way to insert this data without having to create a long function to fix all this?

---

Error Using Insert Into In Access
I have a form and am trying to insert the data from the form fields into the "Registrants" table.

My error occurs at the line:
Set rs = dbs.OpenRecordset(strSql)

The error I get is:
Number: 3219
Description: Invalid operation.

strSql = "
INSERT INTO Registrants (FirstName, LastName, Address, City, State, PostalCode, [Not Mail], EmailAddress, Notes, [Year Certified by IBCLC]) VALUES ('Sam', 'Jones', '1212', 'Anytown', 'IL', '12345', '', '', '', '');
"

When I copy the SQL statement into a blank query, it works fine, although I get a message telling me that I am about to append 1 row to the database, which is fine. Is this message the reason I am getting the error in my code? How do I handle it?

This is probably a simple oversight on my part because I haven't used Access for quite some time (and would rather not, but that's what they insist on.)


Function InsertRecord() as Boolean
Dim dbs as Database
Dim rs as Recordset
Dim strSql as string
Dim strFName as string, strLName as string, strAddress as string, _
strCity as string, strState as string

strFName = StrConv(FirstName, vbProperCase)
strLName = StrConv(LastName, vbProperCase)
strAddress = StrConv(Address, vbProperCase)
strCity = StrConv(City, vbProperCase)
strState = StrConv(State, vbUpperCase)

on error GoTo Err

strSql = "INSERT INTO Registrants " _
& "(FirstName, LastName, Address, City, State, PostalCode, " _
& "NotMail, EmailAddress, Notes, [Year Certified by IBCLC]) " _
& "VALUES ('" & strFName _
& "', '" & strLName _
& "', '" & strAddress _
& "', '" & strCity _
& "', '" & strState _
& "', '" & PostalCode _
& "', '" & NotMail _
& "', '" & EmailAddress _
& "', '" & Notes _
& "', '" & YearCertifiedbyIBCLC & "');"

set rs = dbs.OpenRecordset(strSql)

InsertRecord = true

on error resume next
strSql = ""
rs.Close
set dbs = nothing
Exit Function

Err:
MsgBox ("There was an error inserting this person, try again or contact a system administrator.")
set rs = nothing

End Function




All suggestions are appreciated!
Thanks much,
Kymberlie

---

Access Delay When Insert Data
hello
i have noticed that after i insert data into ms access using vba it takes a ~5 seconds delay to display the new data in controls like a listbox using a normal query,

i want to ask you if it is possible to make the data viewable insantly after the insert

i am not sure if that happens because of access or because some config i'm missing, so i wanted to ask

thanks for your advise

---

Insert Data Access Database
im very new to VB. I created vb forums that lookup data from a acess database...... But how would i inset data from a vb for into a access database... how owuld i start this and are their any examples???

Thanks

---

Insert Data From Excel To Access
I need to insert data from a Excel file to an Access table.

I refer to an old topic here pertaining to that same case and follow the code they'd present their.


Quote:




Dim Cn As ADODB.Connection

Set Cn = New ADODB.Connection

'lets connect to the workbook first, I tested this, it works for me
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.xls;Extended Properties=Excel 8.0;" _
& "Persist Security Info=False"

' Append data from Sheet1 of workbook to Table1 of mydb.mdb:
Cn.Execute "INSERT INTO [Table1] IN 'db1.mdb' SELECT * FROM [Sheet1$]"

Cn.Close
Set Cn = Nothing




But I'm having an error like this...


Quote:




Could not use 'db1.mdb'; file already in use




Why is that?

---

(vb6)Insert Data In The Access Table
Hon'ble gurus,
I use VB6 and Access2002 database and ADODB. I have 2 tables created in my .mdb file. Both the tables have a common field MyCode. I want that a particular data row(which is typed in a textbox) has to be deleted from table1 and should enter in table2. With regard to deletion I use like:
con.execute "delete * from table1 where MyCode= '" & Text1.text & "' "
But, how can I insert that particular data in table2 ? Please advise me. Thanks and regards.



pkb_pkb

---

Data SHAPE Insert Error
Hello All,
heer is the SQL stmt that I am passing to an ACCESS97 DB.But it says "Error in INSERT INTO Statement"


cn.Provider = "MSDataShape.1"
cn.Open "TEST"

SHAPE = "SHAPE {Insert Into Master Values(5,'" & oOrder.Item(1).Name & "'"
SHAPE = SHAPE & "," & "'" & oOrder.Item(1).Address & "'"
SHAPE = SHAPE & "," & oOrder.Item(1).Phone & "}"
SHAPE = SHAPE & "APPEND({Insert Into Detail values(5,'" & oOrderDetail(1).Designation & "'"
SHAPE = SHAPE & "," & oOrderDetail(1).OffPhone
SHAPE = SHAPE & "," & "'" & oOrderDetail(1).Qualification & "'"
SHAPE = SHAPE & "}as Details Relate ID to ID)"

rs.Open SHAPE, cn




Any ideas why???

Thankx in advance...

---

Insert Data Into Access Using Combobox And List Box
rs.Open "INSERT INTO BookReturnTable(Book-ID, Student-ID, ReturnDate, Status, Fine, AmountPaid)Values ('" & TxtBookId1 & "', '" & Text1 & "', '" & Text2 & "' , '" & x & "', '" & TxtTitle & "', '" & TxtAuthor & "')", cn

I'm using such statement to insert the data but no idea in inserting the data using list box and combo box...Need some guidance as well as some example...Thank you

---

Bulk Insert Of Data Into An Online Access Db
Using ASP, is there a way to bulk insert data into an online access db?
Here's the scenario--a salesman enters contact info into his laptop in his truck throughout the day. At the end of the day, I want him to be able to log onto his company website and upload all of the records into an online access db.
Doable?

---

Using SQL INSERT To Place New Data In An ACCESS DATABASE
Could someone please take a look at my program and help! me

I want to write into the database with either the combo boxes or text boxes.... but how do i do it when the database has relasions??.... and with one button....

this is the button code so far...


Private Sub indskriv_Click()
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim titel As String
Dim sprog As String
Dim år As String
Dim selskab As String
Dim skuespiller As String
Dim instr As String

data = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=film.mdb;Persist Security Info=False"

con.Open (data)

Sql = "insert into film (titel) values ('" & titel_ind.Text & "')"

con.Execute Sql


Sql = "SELECT * FROM film WHERE titel = ('" & titel_ind.Text & "')"

rs.Open Sql, con, adOpenDynamic

titel = rs("filmid")

rs.Close

Sql = "insert into selskab (navn) values ('" & selsk_ind.Text & "')"

con.Execute Sql


Sql = "SELECT * FROM selskab WHERE navn = ('" & selsk_ind.Text & "')"

rs.Open Sql, con, adOpenDynamic

selskab = rs("id")


'skuespiller = "insert into skuespiller (fornavn, efternavn) values ('" & rolle_for.Text & "','" & rolle_ef.Text & "')"


'con.Execute skuespiller


'tsu = "insert into Film (Titel, Sprog, Udgivelsesår, selskab) values ('" & titel_ind.Text & "','" & sprog_ind.Text & "','" & år_ind.Text & "','" & selskab & "')"


'con.Execute tsu

titel_ind.Text = ""
sprog_ind.Text = ""
år_ind.Text = ""
selsk_ind.Text = ""

MsgBox "du har tilføjet dit indtastede data"

End Sub


the code is not at all finished.... but i would be glad if someone could help me?.... so DL the "program" and take a peek if you want

it is in danish..... so the button you are looking for is "indskriv".... under "indskriv en film"

---

Copying Data From EXCEL To Access Using INSERT INTO Is This Possible

 I have created a ADO connection to an excel file by the following

 **************************
  Dim xlsConn1 As string
  Dim xls_conn As ADOB.connection
  Dim xls_rs As DOB.Recordset

  xlsConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Source=C: est.xls; Extended Properties = ""Excel 8.0;" & _
 "HDR=YES""""

  Set xls_conn = New ADODB.Connection
  Set xls_rs = New ADODB.Recordset
  xls_conn.Open (xlsConn1)
  xls_rs.Open "Select * from [test$A21:T75]', xls_conn
 **************************

 I would like to take the data range test$A21:T75 and insert it into anaccess database. This is what  I'm tying to do, but I'm having problems with the "...Insert Into..." syntax.


 Here is the code that I'm using

 **************************
Dim acsConn1 As String
Dim acs_conn As ADOB.Connection
Dim acs_rs As ADOB.Recordset

Set acs_conn = New ADODB.Connection
Set acs_rs = New ADODB.Recordset

ascConn1 = "Provider=....

acs_conn.Open (ascConn1)

 ## The Following is the code syntax that I'm having trouble with. I would like to use the "Insert  Into" to copy data from EXCEL into Acess. I was hoping I could use the following statement to do  this, but I'm having issues:

acs_rs.open "INSERT INTO JessTest Select * FROM [test$A21:T75],acs_conn
                          
where,  JessTest is the table that I want to insert the data into


Does anyone have any suggestions? From the errors I'm receiving, it appears that you can only insert data from a table that exsists in data base that you are connected to. In this case with would  be acs_conn. Is this true?

---

Insert Data To MS Access Database From Forms!
Hi Guys.
I am just wondering how I insert data from my window with the textboxes and everything to my table in my Access database.

Also, I have the customerID number on my form and in the database it is an autonumber. How do i show the customers actual customerID when the form window opens?

Cheers and thanks in advance.

---

Insert Data To Access With Visual Basic
Hello guys,


I would like to ask you if you can help me. I have a database wich take tha data with visual basic 6. I want to save tha data (textboxes) into the database. The name of the database is mydb and include the lname,fname and address.


Thanks in advance.

---

Insert Flat File To Access Db Using VB Error On One PC
For some reason the below code works on all PCs but there is one company that it will not work for. They have tried on several PCs and everytime they get an error saying that the query fields are not the same. This usually makes sense and I just replace the database and it works. For some reason the text file matches the database table and it still will not work. I have tested the program on my pc using there databases and it works fine with both examples below. I am thinking that it is something to do with the text driver so I tried the second example below and with that example 2 only the first column of data is insterted into the table. I have used the mdac compenent checker and ther are no mismatch files. Any other suggestions as to what compenent to check or a reason why it will work on hundreds of other customers PCs but not at one location?

example 1
strCn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & App.Path & ";" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=Admin;Pwd=;"
strSql = "INSERT INTO [" & FileName & "] IN '" & dbLocation & "'"
strSql = strSql & " SELECT * FROM " & TextFile
frmftp.adoCn.Execute strSql, a

example 2
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbLocation
Dim counter As String
counter = InStrRev(TextFile, "")
textfile1 = Left(TextFile, InStrRev(TextFile, ""))
textfile2 = Right(TextFile, Len(TextFile) - counter)
textfile1 = "[Text;Database=" & textfile1 & ";HDcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbLocationR=YES].[" & textfile2 & "]"
cn.Execute "Insert INTO " & FileName & " select * FROM " & textfile1

---

Getting Error When Trying Insert Data, Excel To Msflexgrid
Hi all,
I am new to new forum, hope you all cooprate me.

well I am working on vb6.0 and I am trying insert data excel to msflexgrid,
for this I am using "Microsoft excel 11.0 object library"

I wrote this code, and getting error msg "subscript out of range", plz anyone tell me where I am wrong and what should I do for rectify this error.

code---------on clicking on the button--------------------------



VB Code:
Dim RowCNT As Integer, ColCNT As IntegerDim excel_app As ObjectDim excel_sheet As ObjectDim max_row As DoubleDim max_col As IntegerDim Excel_row As DoubleDim Excel_Col As IntegerDim xl As New excel.ApplicationDim xlWB As New excel.WorkbookDim xlWH As New excel.WorksheetDim i As Integer, Column As Integer, j As IntegerDim conn As ADODB.ConnectionDim statement As StringDim new_value As String, new_value1 As StringDim file_path As StringDim FileName As StringDim colvalue As String, colname As String, tempvalue As StringDim Grid_row As Integer, Grid_col As IntegerDim xls As VariantIf txtFilePath = "" ThenMsgBox "Please first select the excel file!!"cmdBrowse.SetFocusElse     file_path = txtFilePath.Text     Set excel_app = CreateObject("Excel.Application")     excel_app.Workbooks.Open FileName:=txtFilePath.Text     If Val(excel_app.Application.Version) >= 8 Then        Set excel_sheet = excel_app.ActiveSheet     Else        Set excel_sheet = excel_app     End If     max_row = excel_sheet.UsedRange.Rows.Count     max_col = excel_sheet.UsedRange.Columns.Count     Screen.MousePointer = vbHourglass     DoEvents     flxGrd.Clear    flxGrd.Rows = 2    RowCNT = 0    ColCNT = 0    'first loop for rows       For i = 1 To max_row    'second loop for columns    For j = 1 To max_col        With flxGrd        .TextMatrix(i - 1, j - 1) = _            excel_sheet.Cells(i, j).Value & " "        End With    NextNext   Screen.MousePointer = vbDefault End If



----------------------------------end of code-------------------------

thanks in adwance

Regards
Varun J

---

Insert,delete,update Data To Access Db In Lan Environment
i always got an error message "Operation must use an updateable query" whenever i tried to add,delete,update,edit data to my access database which was located in our local server.im making a vb application as front end and soon i will install it in 5 workstations and my access database as my back end is located in the server.i have this error "Operation must use an updateable query" whenever i tried to add data to the database.

how can i solve this problem?is there any simple solution regarding this?
your soonest reply would be very much appreciated

thanks and good day!!

---

How To Insert The Raw Data From A Text File To A Microsoft Access Using VB
I need to develop a application using VB. I have created a database
using Microsoft Access. Now i want to insert the data from a text file.
I want to do that using VB. plz help me. How to develop that?

---

Problem When Retrieving Data From Access And Insert Into SQL Database
Hi,

Iam trying to retrive data based on certain criteria and insert into data base in sql server of same structure as access database.

Everything works fine on a workstation with win 98 OS. But when I run the exe on the server it self (win 2000 server) I face the below runtime error:
Runtime Error ‘-2147467259’ ODBC – call Failed

Below is the part of code where I encountered the error. Similar to below statements I have multiple selection & inserts. Errors occurs some time for first table insert and some times 2/3rd.

sql1 = "select * from Document_Master"
rsSql1.Open sql1, dbconn, adOpenForwardOnly, adLockReadOnly
If rsSql1.EOF = False And rsSql1.BOF = False Then
Do While Not rsSql1.EOF
sql2 = "Select * from [" & mysqlconn & "]. [Document_Master]
where Location_Code = '" & rsSql1!Location_Code & "'

rsSql2.Open sql2, dbconn
If rsSql2.EOF = True And rsSql2.BOF = True Then
SQL = "Insert into [" & mysqlconn & "].[Document_Master]
select * from Document_Master where Location_Code = '"
& rsSql1!Location_Code & "'
dbconn.Execute SQL

rsSql2.Close
rsSql1.MoveNext
Loop
End If
rsSql1.Close


Should I go for DTS? If I incorporate script generated by DTS Schedule, after creating package for my application will it work on a PC with out VB & MS SQL Server??

Please help!

Thanks

---

Whn I Type ' At My Textbox That Want Insert Into MS Access It Come Out Run-time Error
Whn I type ' at my textbox that want insert into MS Access it come out run-time error

how to solve this problem... error msg something like missing operator=''dasda','dasdd','das',...

---

Error Message When INSERT A Data To The SQL Server 2000
Dear all,

Good Morning. When i INSERT a data in the SQL Server 2000, it will pop up this error message...

Code:Run-time error '-2147217865(80040e37):
Invalid object name 'GPGTesting'.

Anyone, please help me on this, is my coding got problem...

My table name is "GPGTesting" and 1 field name is "program" (Data Type=varchar, Length=50 & Allow Nulls)...

Code:Private SQLOn As ADODB.Connection
Private SQLConn As New ADODB.Recordset

Set SQLOn = CreateObject("ADODB.Connection")
SQLOn.ConnectionString = "Provider=SQLOLEDB.1;Password=nam;Persist Security Info=True;" & _
                 "User ID=nam;Initial Catalog=PICS1;Data Source=43.74.13.103"
SQLOn.Open
sqlCall = "INSERT INTO GPGTesting (program) VALUES ('" & abc & "')"
SQLConn.Open sqlCall, SQLOn, adOpenStatic, adLockReadOnly
SQLConn.Close
SQLOn.Close
sqlCall = ""





Edited by - AbbydonKrafts on 2/28/2007 5:56:16 AM

---

How To Load Excel Data Into ASP Page Den Insert Each Line Into Access One By One?
how to open an existing excel file with multiple columns and rows and den insert it into ASP form den upload each record into database. Had to do it in this way cos when upload to database, it require to perform other tasks.

Or how to open an excel file and load its contents directly into an existing access table?

Please provide me the code to teach me. Thanks!

---

Insert Access Data Into Word Form Table Cells
Hi everybody

I have a Word form template with a vba command button (“User Name”) placed in a table cell. The command button displays a vba form with a list of employee names taken from an Access table along with a “Cancel”, “Clear” and “Ok” button. A user selects a name which is then inserted in an adjacent table cell on the word form.

Just this above-mentioned procedure has taken me Weeks to put together. Most of the code and advice came from the VB City forum, so I have you guys to thank for everything.

So, back to my plea for help. The form template also contains word text fields that users must complete, for example, job title, unit and personnel number etc. However, this information is also available in the Access table. So I thought to myself, wouldn’t it be great if this data could somehow be inserted into the various table cells on the word form instead of using basic text fields.

Currently, I can only manage to call one column of data from the Access table and populate a dropdown list and insert one name into a table cell (see code below). I would love to be able to list all Access table details and for those details to be placed in various table cells on the form.

Any assistance would be greatly appreciated.

Kind regards

Arabella




Code:Private Sub EMSA_Staff_Clear_Click()
    ClearCell
End Sub

Code:Private Sub EMSA_Staff_Ok_Click()
Dim i As Integer
Dim s As String
Dim strSel2 As String
Dim strOther2 As String
'ActiveDocument.ListBox1.Clear
    s = ""
    s = ActiveDocument.Tables(1).Cell(2, 2).Range.Text
    s = Mid(s, 1, Len(s) - 2)
    If Len(s) > 0 Then
        s = s & ", "
    End If
    ClearCell
For i = 0 To Me.ListBox1.ListCount - 1
   If Me.ListBox1.Selected(i) = True Then
      strSel2 = Me.ListBox1.List(i) & vbNullString
      If strSel2 = "NON-EMSA Personnel" Then
         strOther2 = InputBox(prompt:="Please enter name.", Title:="Non-EMSA Personnel")
         If InStr(1, s, strOther2 & ", ", vbTextCompare) = 0 Then
            s = s & strOther2 & ", "
         End If
       Else
         If InStr(1, s, strSel2 & ", ", vbTextCompare) = 0 Then
            s = s & strSel2 & ", "
         End If
      End If
   End If
Next i
    If Len(s) > 0 Then
        s = Mid(s, 1, Len(s) - 2)
        ActiveDocument.Unprotect
        ActiveDocument.Tables(1).Cell(2, 2).Range.InsertAfter (s)
        ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
    Else
        ClearCell
    End If
      
 Unload Me
End Sub

Code:Private Sub Lead_Emsa_Person_Cancel_Click()
    Unload Me
End Sub

Code:Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=S:ITemsa staff details.mdb"
    rst.Open "SELECT DISTINCT [Name] FROM Staff ORDER BY [Name];", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ListBox1
        .Clear
        Do
            .AddItem rst![Name]
            '.AddItem rst![Title]
        rst.MoveNext
        Loop Until rst.EOF
    End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

Code:Private Function ClearCell()
    ActiveDocument.Unprotect
    ActiveDocument.Tables(1).Cell(2, 2).Range.Delete
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End Function

---

HELP NEEDED!!! Problem In INSERT INTO SQL Statement. Copy Data From Excel To Access
To all EXPERT programmers out there

need a solution in VB on how to copy some column infomation in excel into predefine fields created in access using DAO or ADO (preferably DAO)method

My excel sheet (BOM.xls) contains 12 columns but i need only data in 6 columns to be inserted into BOM table in abc.mdb access database.

Excel columns
Idn
Lvl
Find No
Item Typ
Item No
Item Rev
Item Desc
Qty
UM
BOM Type
Item Status
Notes

BOM table structure
DocNo
FindNo
PartNo
Location
Alt
Desc
Qty
SmtQty
CadQty
Flag
fileId
pbaNo
Rev
prod
Family
Datestamp
PartType
StockRoom
Remarks

Needed information
Excel columns Access field

Find No FindNo
Item No PartNo
Item Rev Rev
Item Description Desc
Qty Qty
Notes Location

Rest of the fields can be hard coded with a fix value and date of insert

Below is the solution i was working on but encountered problem

Is this SQL comand correct? Prompt INSERT INTO syntax err when execute

"INSERT INTO Books (FindNo,PartNo,Location,Desc,Qty,Rev)
VALUES (1,866469, U1J1','MOTHER BD',1,02)"

This is the code to build this query.

Dim excel_app As Object
Dim excel_sheet As Object
Dim max_row As Integer
Dim max_col As Integer
Dim row As Integer
Dim col As Integer
Dim conn As ADODB.Connection
Dim statement As String
Dim new_value As String

Screen.MousePointer = vbHourglass
DoEvents

' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")

' Uncomment this line to make Excel visible.
' excel_app.Visible = True

' Open the Excel spreadsheet.
excel_app.Workbooks.Open FileName:=(dirBom & "" & TxtBom.Text)

' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If

' Get the last used row and column.
max_row = excel_sheet.UsedRange.Rows.Count
max_col = excel_sheet.UsedRange.Columns.Count

' Open the Access database.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "ikmeso1.mdb" & ";" & _
"Persist Security Info=False"
conn.Open

' Loop through the Excel spreadsheet rows,
' skipping the first row which contains
' the column headers.

For row = 6 To max_row
' Compose an INSERT statement.
statement = "INSERT INTO Books (FindNo,PartNo,Location,Desc,Qty,Rev) VALUES ("

For col = 1 To max_col

If col > 1 Then statement = statement & ","
'statement = statement & "'" & _

new_value = Trim$(excel_sheet.Cells(row, 3).Value) & "," & _
Trim$(excel_sheet.Cells(row, 5).Value) & "," & _
Trim$(excel_sheet.Cells(row, 12).Value) & "," & "'" & _
Trim$(excel_sheet.Cells(row, 7).Value) & "'" & "," & _
Trim$(excel_sheet.Cells(row, 8).Value) & "," & "'" & _
Trim$(excel_sheet.Cells(row, 6).Value) & "'"

statement = statement & new_value
conn.Execute statement, , adCmdText Next col

Next row

' Close the database.
conn.Close
Set conn = Nothing

' Comment the Close and Quit lines to keep
' Excel running so you can see it.

' Close the workbook saving changes.
excel_app.ActiveWorkbook.Close True
excel_app.Quit

Set excel_sheet = Nothing
Set excel_app = Nothing

end sub

Any help is much appreciated.

Thanks a million!!!

---

[access Vba]Problem With An Insert Query In A Memo Field: Run-time Error 3075
Hi,
I've a problem with a simple insert query when the number of characters inserted into a memo field is hight.
this is my code:

Code:
...
Dim fso As New FileSystemObject, ts As TextStream
Dim s As String, sql As String
Dim db as Database
Set db as CurrentDb()

Set fso = CreateObject("Scripting.FileSystemObject")

For i = 1 To selFiles

Set ts = fso.OpenTextFile(.SelectedItems.Item(i), ForReading)

s = ts.ReadAll

ts.Close

sql = "INSERT INTO documenti (lid, dtitle, dtext, ddata) VALUES (" & _
Me.lang.Column(0) & ", '" & _
Replace(nof(.SelectedItems.Item(i)), "'", "''") & "', '" & _
Replace(s, "'", "''") & "', '" & _
Now() & "')"

db.Execute (sql)

Next i
I read a text file and I insert its contents into my database, but when the length of variable 's' is more than 18/20,000 chr I got runtime error 3075, but a memo field could contain up to 65,000 chr ca.
Anyone could suggest me where is the problem?

---

RunTime ERROR '6160' Data Access Error
RunTime ERROR '6160' Data Access Error

Does anyone know where i can find out what this means? Ive tried the official microsoft site without much luck

any help would be greatly appreciated

thx

---

RunTime ERROR '6160' Data Access Error
RunTime ERROR '6160' Data Access Error

Does anyone know where i can find out what this means? Ive tried the official microsoft site without much luck

any help would be greatly appreciated

thx

---

Insert Currency Data Type To MS Access Currency Field
Im want to insert a into a currency field in ms access using vb 6.0 but i keep gettin an error message. What should i place around the value to denote insert it.
here is my code:



VB Code:
Dim sqlstmt As String            sqlstmt = "INSERT INTO tbl_Products (ProductName,ProductDesc,SalesPrice,SpecialOffer,SpecialOfferPrice) VALUES (" & _              "'" & p_prodName & "', " & _              "'" & p_prodDesc & "', " & _              "'" & p_sPrice & "', " & _              "'" & p_specialOffer & "', " & _              "'" & p_soPrice & "' " & _              ")" MsgBox sqlstmt    db.Execute (sqlstmt)

---

Insert Sql Wont Insert Data After Decimal
i have a form that uses a Calculation script to do the vat calculation

Code:Option Explicit ' Requires variables to be declared before they are used

Rem * Declarations ***********************************************************
Dim dblNumber As Double 'Number Entered by user
Dim dblResult As Double 'Result of calulation
Dim intOption As Integer 'Operator held (+,-,*,/)
Dim dblMemory As Double 'Number stored in the Memory
Dim blnOption As Boolean 'Number","+","."
Dim blnEqual As Boolean 'Equals error
Dim blnMemory As Boolean 'Memory error
Dim blnError As Boolean 'If Point '.' is press more than once
Dim strVat As String 'VAT user number entered
Dim dblTotal As Double 'VAT Total
Dim strAnswer As String 'VAT Answer


Public Sub VATCALC()
On Error Resume Next

dblTotal = (Invoicesto.Price.Text * Invoicesto.Vatpc.Text) / 100 '* strVat '(txtSubTotal.Text * 17.5) / 100

Invoicesto.exvat.Text = Invoicesto.Price.Text - dblTotal 'Round(strAnswer, 2)
End Sub

 VATCALC works, however when i add the product to the table the calulations have no decimal and data after it.

---

Data Access Error
I wrote a small application to act as the front end to a Access database.
Everything runs great on the the machine that I created it on. I used an installer program to make an setup.exe. I did install all the dll's, ocx's. When I install it on the other computer and open the exe. I get a small message box that say's Data Access Error with an OK button. What can I do to fix this?

My computer is XP w/ Access 2000
Other computer is NT w/ Access 97

---

Data Access Error
hi,
I create a data object and I change the databasename property to the database I want to use.However when I start the program I get a "Data access error".I also tried a sample source code and I get the same error.
The files are not read only.Is there any way to fix that?

---

Data Access Error
hi i am getting this error in my data grid once i click on it
any idea why?

run time error 6160

data accesss error
using sql 2000

---

Data Access Error
Hi Team,

I created a simple vb program using data connecting to a access mdb

code was

sub...

with data1
.databasename = app.path &(" est.mdb")
.recordsource = "test"
end with

end...

created a link for a text field
for testing created few records.. as one, two, three.

when executed as executables, its working fine in My Pc. but passes error as "data access error" in other PC( without VB).

I tried installing MDAC 2.7, But yet Problem Presists.
Pls Help Asap.

Its Making me go Mad...

---

Please Help:525 Data Access Error
im using vb6 with ms access 2000
i add msflex grid
add data1
the error occur in line data1.refresh (525 data access error)
i get update sp6
pleaaaaaseeeeeeeeeeeeeeeee any help this error will kill me...........

---

Data Access Error
hi there,
I am using VB based program and I don't have its source code (although I developed it using VB6 long time ago). It used to be working on my PC before but now I am facing the problem that when I access a part where there is database retrieval, first it shows: "Data Access Error" and then
"Run-time error '91':
Object variable or With block variable not set"

Does anyone knows the solution?
Regards

---

Data Access Error
I am getting a Data Access Error and an Item Not Found in Collection Error when i run the following code:

Any Ideas????



VB Code:
Set pubDbBatch = DBEngine.OpenDatabase(datBatchHeader.DatabaseName)  Set prvRsBatchHeader = pubDbBatch.OpenRecordset("BatchHeader") [b] Set datBatchHeader.Recordset = prvRsBatchHeader[/b]


The error pops up after the line in Bold


prvRsBatchHeader is my recordset
datBatchHeader is my data control
pubDBBatch is the Database

---

Data Access Error
VB Code:
Option Explicit Private daoDB36 As Database Private rs As DAO.Recordset  Private Sub Form_Load()  Set daoDB36 = DBEngine(0).OpenDatabase(datBatchHeader.DatabaseName) Set rs = daoDB36.OpenRecordset("BatchHeader") Set datBatchHeader.Recordset = rs  End Sub



When i run this code at the Set datBatchHeader.Recordset = rs line i get a Data Access Error message box returned

---

Data Access Error
Please help me with this,

Ive been trying to deploy a vb application which uses data controls (dao350.dll) . At first i got the classic cant find clsid error but i fixed this copying the dao350.dll file and registering it. But now i get a "data access error". The thing is on computers that had vb installed and then removed it works fine, but on those that never had vb installed i get the data access error.

I wonder if any of you has had this problem.

---

Data Access Error
Data Access Error ...?

---

Data Access Error In XP
I have written an app which uses the ms data control for access 97
(dao350.dll)

if installed on windows 2000 the program runs perfectly,
but when installed on windows XP I get a "Data Access Error" when I run the
program

I have updated both machines to MDAC 2.8 and I have updated the Jet 4.0 to
the latest version
but still I get this problem under XP

Any ideas to solve this would be greatly appreciated as it is now driving me
insane!

--
Best Regards
Rod Pearce

Join Bytes!

---

Help With Data Access Error
Hello Community.

Could some help me? I recieved the following error: Runtime error. No value given for one or more required parameters.

Attached, you can find the code that this error is referring to.


Thanks,
Chris

---

The Data Access Error
Hi,
I had a program using an Access DB, today it suddenly stopped working with the "Data Access Error" (Runtime Error '525')! Any Ideas what the problem might be and how to solve it?

Regards,
Prakhar Birla

---

Data Access Error
I created a simple vb 6 program using data connecting to a access mdb
works fine.

 The prioblem arises when I try to get the data to a crystal report at run time using a data object . i asignn the data source of crystal control to data object. This too works fine. But when i make setup file and install on win xp (works on other flavour of window) it p passes error as "data access error"

I tried installing MDAC 2.5, But yet Problem Presists.
Pls Help Asap.

Its Making me go Mad...

(I hope I get help today)

---

Data Access Error
I wrote a small application to act as the front end to a Access database.
Everything runs great on the the machine that I created it on. I used an installer program to make an setup.exe. I did install all the dll's, ocx's. When I install it on the other computer and open the exe. I get a small message box that say's Data Access Error with an OK button. What can I do to fix this?

My computer is XP w/ Access 2000
Other computer is NT w/ Access 97

---