Open and update a SQL table

I have a table on a SQL server called history. When I link this table
using an ODBC connection, i give it the username and password and tell
it to save the password and it works great. On my PC. Other computers
however get asked for the password each time they try to write records
to the table.

What I'm trying to do is remove the linked table and open the SQL
database in a module and use the INSERT INTO query to insert my records
and then close the DB connection.

Is this the best way to do this? Can someone provide me some sample
code that will open a SQL database using ODBC and then insert some
records into a history table and then close it all down?

I'm using Access97 and SQL 2000.

Thanks !

re: Open and update a SQL table

"Bruce Lawrence" <dgr8koolaid@hotmail.com> wrote in message
news:1115899981.031221.63490@g43g2000cwa.googlegro ups.com...[color=blue]
>I have a table on a SQL server called history. When I link this table
> using an ODBC connection, i give it the username and password and tell
> it to save the password and it works great. On my PC. Other computers
> however get asked for the password each time they try to write records
> to the table.
>
> What I'm trying to do is remove the linked table and open the SQL
> database in a module and use the INSERT INTO query to insert my records
> and then close the DB connection.
>
> Is this the best way to do this? Can someone provide me some sample
> code that will open a SQL database using ODBC and then insert some
> records into a history table and then close it all down?
>
> I'm using Access97 and SQL 2000.
>
> Thanks ![/color]

How is the SQL Server set up? With Windows/Built-in/Integrated security
only or does it allow SQL authentication - and which do you use? It makes
it easier for you if it's Windows authentication only since you don't have
to worry about storing passwords, but if you use SQL authentication what do
you feel about passwords and security? Should people log in each time - or
could a password be stored?
Finally have you considered ADO for this if there is no real need to have
linked tables - just an application which does inserts.

re: Open and update a SQL table

I'm using SQL authentication. The login name is "mplrpt" and there is
no password.
The password could be stored. I'd rather not link the table in Access.
I'd rather open the DB in VB code and insert the records directly.

I'm not sure really about ADO or how it would work in this instance.
All I'm doing now is sifting through sample code in Access and trying
to adjust it to my needs but its not working heh.

re: Open and update a SQL table

"Bruce Lawrence" <dgr8koolaid@hotmail.com> wrote in message
news:1115904494.219743.246620@g49g2000cwa.googlegr oups.com...[color=blue]
> I'm using SQL authentication. The login name is "mplrpt" and there is
> no password.
> The password could be stored. I'd rather not link the table in Access.
> I'd rather open the DB in VB code and insert the records directly.
>
> I'm not sure really about ADO or how it would work in this instance.
> All I'm doing now is sifting through sample code in Access and trying
> to adjust it to my needs but its not working heh.[/color]


Well you could try this sort of approach (which uses late binding to avoid
mentioning references)

Private Sub ADO_ADD()

On Error GoTo Err_Handler

Dim cnn As Object
Dim strCnn As String
Dim strSQL As String
Dim lngRecords As Long

strCnn = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDatabase;" & _
"User ID=mplrpt;Password=;"

Set cnn = CreateObject("ADODB.Connection")

cnn.ConnectionString = strCnn

cnn.Open

strSQL = "INSERT INTO tblTest (MyText, MyNumber) VALUES ('Hello', 22)"

cnn.Execute strSQL, lngRecords

MsgBox lngRecords & " record(s) added", vbInformation

Exit_Handler:

If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

re: Open and update a SQL table

Awesome !

Without going into great detail, can you explain the difference between
what you posted and this:

Set mplwksp = CreateWorkspace (blah blah)
Set mplhistory = mplwksp.OpenDatabase (blah blah)

Thank you so much !

re: Open and update a SQL table

Well, slight problem just came up...

When I changed your SQL statement to mine, I get an error

Line1: Incorrect Syntax near ","

This is my statement
SQLstr = "INSERT INTO history
(clockno,job,empname,ordno,curdate,machno) VALUES" & _
"('" & Packer & "','Packer'," & packname & "," & order & "," &
Now() & "," & machno & ")"

re: Open and update a SQL table

"Bruce Lawrence" <dgr8koolaid@hotmail.com> wrote in message
news:1115911202.409947.327440@z14g2000cwz.googlegr oups.com...[color=blue]
> Well, slight problem just came up...
>
> When I changed your SQL statement to mine, I get an error
>
> Line1: Incorrect Syntax near ","
>
> This is my statement
> SQLstr = "INSERT INTO history
> (clockno,job,empname,ordno,curdate,machno) VALUES" & _
> "('" & Packer & "','Packer'," & packname & "," & order & "," &
> Now() & "," & machno & ")"[/color]

Do a debug.print SQL and the problem should become clear. It will be quotes
or similar. For the dates use
'2005-12-25' format

re: Open and update a SQL table

Thanks for the reply Justin

The way I fixed it was this:

curdate = Now()

I also made sure my datatype on the SQL server matched the format and
its working great now !

Thanks for all the help. You saved me tons of work