We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

From Access to MSSQLSERVER

mativare
mativare asked
on
Medium Priority
213 Views
Last Modified: 2010-05-03
Using VB5, MSSQL server 6,5,   DAO
I have mdb database and linked with server exsisting empty table(dbo_cheque)I wanted to move table contents to MSSQL server
DAO
sql = "INSERT INTO dbo_cheque SELECT * FROM chequeproper;"
 db.Execute sql

I am getting error message 3070 : MS Jet does not recognize "aaaa"(my mdb fieldname) as a valid field name
I tried to use several field names over there, result the same.
Any help appreciated
Comment
Watch Question

Commented:
Your syntax needs to be:

Insert Into dbo_cheque (field1,field2,field3...) select fromfld1,fromfld2,fromfld3... from fromtabl [optional where clause]

You need to enumerate the fields inserted.  You need to have rights on the server.  You need to include all necessary key fields, foreign key constraints, not null fields etc to make it happen.

Commented:
Be sure to have the same number and type of the fields in both dbo_cheque and chequeproper. They have to be at the same order as well for your Qry to be executed correctly.
What you really need is the Access to SQL Upsizing tool from Microsoft.  

Commented:
Another way is to physically go to access and export the tables to MSSQL .

Author

Commented:
Ineresting
But I cannot use Upsizer in this case, because I insert data in run time, it means every time client enters row data to excel spreadsheet(linked to Access), it inserts row SQLserver too(linked via Access)
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.