-1

I am building a C# project that transfers data from an Access database to an SQL database. I am using this command:

sqlMergeDB = "Insert int work.sql_merged_database.dbo." + tablename[a] + 
"SELECT * FROM acs_merged_database.mdb." + tablename[a];

It keeps saying it doesn't recognize the Access table object and I am a thousand percent sure the table name is correct and there is a connection to the database. Maybe I don't know a certain syntax for listing Access data objects.

More specifically the error is invalid object name.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
Ben C.
  • 31
  • 4
  • Does the SQL database have a connection to the Access database? Or is the connection only in your code? – StarPilot Jun 16 '15 at 20:35
  • my code makes two open and successful connections to the sql database and the access database – Ben C. Jun 16 '15 at 20:36
  • 2
    Then you cannot tell the SQL database to use the Access database table. The SQL database does not have the connection. You need to read in the Access database information into your code, and then dump it into the SQL database. That is why you are getting your error. – StarPilot Jun 16 '15 at 20:38
  • 1
    Your code "Insert Int ..." should at least be "INSERT INTO ..." – NoChance Jun 16 '15 at 20:40
  • The best way is to export from Access and import into SQL Server using utilities. – NoChance Jun 16 '15 at 20:42
  • Open your read Access query, and walk through it one row at a time. Read in its values, then insert them into your SQL database table. – StarPilot Jun 16 '15 at 20:42
  • Okay great but I don't know how to read in one row at a time from access, and then set it equal to one row in sql in C# Could you guide me to the code or maybe the functions you'd use. – Ben C. Jun 16 '15 at 20:44
  • Find out how to query Access data from C#. Then find out how to perform an Insert in SQL. You can Bing/Google that or just look it up here on Stack Overflow. You can also Bing/Google "SQL Insert Row" to will reveal many links of tutorials on how to do an Insert using SQL. That is the basic way. Or search here for how to do it. What you need to do has been done millions of times before you, so the internet and stack overflow are both full of examples and tutorials. – StarPilot Jun 16 '15 at 21:35

1 Answers1

1

Ben,

you should create a Linked Server in your SQL server to contact the Access db, for this step you can check this: SQL to Access linked server

Then you can run your insert statement as:
INSERT INTO work.sql_merged_database.dbo.TableA (ColumnA, ColumnB) SELECT ColumnA, ColumnB FROM LinkedServerName.acs_merged_database.SchemaName.TableName

Community
  • 1
  • 1
Renato Saksanni
  • 36
  • 2