I have an desktop applications in VB.NET Visual Studio 2008. I am doing Insert and update queries to Access 2000 - 2003 format. I figured out that you need to keep your update queries under I think 127 fields. My table in Access has 155 fields so I split the update query into two seperate queries and that fixed my update issues.
Now I have INSERT query issues. When I open my access database and open the table I want to do the insert query it opens just fine. After I try an INSERT query of all 155 fields from my desktop application that table will not open anymore and tells me it is unrecognizable. All of the other table in the database still open up fine but that one is unrecoverable.
My question is there a limit to how many fields you can have in an INSERT Query from my desktop application into the Access database?
INSERT INTO myTable
(my 155 fields)
VALUES
(my 155 values)
If I run the exact same query on my SQL Server it checks out fine and executes perfectly. I have the same table in SQL Server as I do in Access. I have a client that wants desktop updates to control who can update the SQL Server. So just forget about SQL Server for any of your answers I have to do this thru Desktop application and Access.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
AFAIK the same, have a look at the following
http://www.databasedev.co.
I checked the INSERT query line by line and matched everything up. It is 100% spot on. I even when back and cut the query down to 10 fields and got the same issue so I am now thinking it is not number of fields. Below is and example of what I am using not actual query. Also I have matched oup different data types to make sure I am not sending bad query string. This has got me stumped because I have another application I wrote with the same concept with the same copy of this database file and it works everytime.
INSERT INTO myTable
(field1, field2, field3, field4)
VALUES
('hello1', 'hello2', 'hello3', 'hello4')
Dim myCommand As New OleDb.OleDbCommand(stQuery
myCommand.Connection = cnmydatabase
cnmydatabase.Open()
myCommand.ExecuteNonQuery(
myCommand.Connection.Close
Idealy this below fixed it. I have code to compact and repair the database when the apps start. However during my debugging I shut this off and that cause a different issue.
tbsgadi above had the right idea of compacting however I used what I already had not the link in tbsgadi post.
Dim acJet As JRO.JetEngine
acJet = New JRO.JetEngine
acJet.CompactDatabase("Pro
"Data Source=" & Apath & "\myaccessfile.mdb;" & _
"Jet OLEDB:Database Password=mypassword;", _
"Provider=Microsoft.Jet.OL
"Data Source=" & Apath & "\copymyaccessfile.mdb;" & _
"Jet OLEDB:Database Password=mypassword;" & _
"Jet OLEDB:Engine Type = 5")
'delete the old\original database
Kill(Apath & "\myaccessfile.mdb")
'Rename the new compacted database back to the original name
Rename(Apath & "\copymyaccessfile.mdb", Apath & "\myaccessfile.mdb")
Business Accounts
Answer for Membership
by: tbsgadiPosted on 2009-03-16 at 00:55:33ID: 23895380
Hi iamtgo3,
You can have up to 255 fields in a Table.
It looks like you've got some other problem going on.
Maybe try decompiling & compacting
http://www.granite.ab.ca/a
Gary