Originally Posted by
tcoombes
Access and SQL Server work fine with T-SQL
From Access you need to create a connection to the server then execute the SQL statement from a string variable
Suppose you want to insert something into an SQL Server Table called Fred
Create Connection
Dim db As ADODB.Connection
Dim Mysql as string
Dim MyInt as Integer
Dim MyText as Sting
Myint= 1
MyText = "Something"
Set db = CurrentProject.Connection (This assumes you are assuming using an access ADP or you will have to set up a connection if using an MDB)
If you are using an MDB you will have to do DB.Open and put in the connection string to open a connection to the server. see at the bottom.
Mysql = "Insert Into FRED (Integerfield1,TextField2) values (" & MyInt & ",'" & Mytext & "')"
db.Execute Mysql
This will execute the statement and put the values in SQL Server in table Fred
If you need to connect to the server from an MDB you have to specify the connection string and open it first
With db
.ConnectionString = "Provider=SQLOLEDB; " & _
"Data Source=" & ServerName & "; " & _
"Initial Catalog=NAMEOFDATABASE;" & _
"User ID=sa; Password=; Trusted_Connection=yes"
.Open
End With
Then you can execute DB commands