Writing from Access to SQL Server using ADODB.Connection

[BennyLinton]

thanks for any help... I am about there... I'm trying to write the entire contents of an Access table to a SQL Server table... So far I can successfully hardcode values, but I need to be able to use a Values SELECT ([Id],[FName],[LName]) from my local Access table... any ideas?

Private Sub UploadToSQL_Click()

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection

cnn.ConnectionString = CONN_STRING
cnn.ConnectionTimeout = 30

cnn.Open

Set rs = cnn.Execute("INSERT INTO GCDFImport ([origDBId],[First Name],[Last Name]) Values (1,'Angus','Tyree')")

rs.Open
cnn.Close

End Sub

My Connection string is in a module:

Public Const CONN_STRING As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog= CC_Search;Data Source=ServerXX"

 

[Ranman256]

you CAN do this without code....
attach the sql table linked into your db.
then just run an append query.

 

[BennyLinton]

Most of my Access databases have ODBC connections, but are a nightmare to install the .reg files or set the security within Access... so I really need to work this out with VBA this time... Thanks

 

[Galaxiom]

You could avoid having to install a DSN for a linked table by using DSN-less connections on the linked table.

Set the security within Access???

To achieve the goal by your requirement you would open a recordset on the source data, iterate through it and concatenate the values into the Execute command in a loop.

BTW No point opening a recordset with the Execute. Simply Execute it.

 

[Simon_MT]

If you have SQL Server SSMS there is an import or export facility.

Import Ace or Jet database and store as Sql Server Native vxx format.

You will still need to create PK Primary Keys, Indexes and any FK Foreign Keys within SQL Server.

Simon