Append record from Access file to table in SQL Server

[H1004]

I have an Access (.mdb) database called (LocalData.mdb) with just tables & no form. When it opens, I want it to append the record to another database, which happen to be connected to the SQL Server. What I did was create an append query. Down at the bottom
I clicked Another Database. And it wants me to enter the path. Then I enter
the path C:\Program Files\DatawarehouseData.adp
Somehow this method doesn't work. It gives me an error message that said “Unrecognized database format: PathName….” Please advise. Any suggestion is greatly appreciated.

 

[cmmrfrds]

One way is to open an ADO connection to sql server and use the sql server function openrowset. The openrowset allows you to reference the Access MDB from sql server. You could use an insert into or select into sql statement. Here is an example, which is doing a join of the Access Northwind Orders table to the Customer table on Northwind database on sql server.

Public Function rowset()
'-- set reference to ADO library
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function

 

[H1004]

I’m sorry, using ADP file is really new to me. So please accept my apology for not understanding & repost the question. When you said open an ADO Connection to SQL Server, does that mean I do all this in the SQL Server, my ADP file, or MDB file?? I’m on an end user side, so I don’t get to play with the SQL Server piece. If I want to implement this code, I have to give it to my DBA.
Again, thank you very much for all your help, and I hope I didn’t offend you cmmrdfrds.

 

[cmmrfrds]

If you are not familar with VBA coding, then I suggest using the Linked Table method in your MDB file. First setup an ODBC connection to the sql server database on the PC that has the MDB file. In the MDB file go under file and use the external tables and Link the sql server tables in your MDB using the ODBC that you setup. Once the sql server tables are linked you can use the QBE grid to create update queries.

 

[H1004]

That's where I have trouble because when I go to file in my MDB file, it has option Get External Data, then Link Table. Then I went and select the ADP file with the SQL Server table, but it gave me an error msg saying "Links can only be created between MS Access database files". Should I click on my ODBC instead of ADP file? If that is the case, where would the ODBC commonly stored?

 

[cmmrfrds]

First, you need to setup an ODBC connection to the sql server database. This is done through either the Control Panel or Administrative depending on the OS of your PC. When you have the ODBC setup then under the external tables use the ODBC option which will be on the bottom of the drop down list.

 

[H1004]

What version of Access are you using? When I click File, Get External Data, the only two option I got is Import and Linked Table. Nothing about ODBC. And I already setup an ODBC connection to SQL Server too.

 

[cmmrfrds]

Click on linked table and when the dialog box appears there will be a drop down list called file type. On the drop down ODBC will appear on the bottom.