Excel/VB insert to remote SQL DB?? 
Author Message
 Excel/VB insert to remote SQL DB??

Hi,

I'd like to use an Excel VB Macro to insert a new record into a SQL table on a
remote SQL Server. The record inserted would use values from fields in a data
entry template in an Excel spreadsheet. Has anybody successfully done this?
Does it require the client machine to have any special registered dll's? I
thought I was on the right track with the code below, but it's not working... I
don't have the objects defined correctly. Any hints/suggestions? If anybody has
any sample code I would very much appreciate it! By the way, the SQL Server is
running SQL Server 6.50.479, on running Windows NT 4.0 SP6 (Build 1381), with
MDAC 2.6, IIS 4.0, twin Pentium 266's, and 384mb of RAM. Its ODBC configuration
allows access to the SQL databases via TCPIP or named pipes.

Thanks,
John Stanmeyer
----------

Sub SubmitInfo()
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=ServerName; Database=testdatabase;
Uid=sa; Pwd=;"
Sql = "INSERT INTO Test_Table (Item_Type) VALUES('Test')"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing
End Sub



Sat, 06 Dec 2003 03:47:38 GMT  
 Excel/VB insert to remote SQL DB??
Hi John,

Which lines raise what errors?  Your code uses late binding.  Have you
considered using early binding, and making a reference to the ADO object
library?  You could then declare dbConn as a Connection instead of an
object, and use the New keyword instead of create object.  It might be a
little easier to debug, and autocomplete always helps.  Something like this:

Sub InsertData()
Dim cnn As Connection
Dim ConStr As String, Sql as String

 ConStr = "Provider=sqloledb;Data Source=ServerName;Initial
Catalog=Sales;User Id=sa;Password=;"
 Sql = "INSERT INTO Test_Table (Item_Type) VALUES('Test')"

 Set cnn = New ADODB.Connection

 With cnn
  .Open ConStr
  .Execute(Sql)
  .Close
 End With

 Set cnn = Nothing

End Sub

--
Jeff McAhren
Dallas, Texas


Quote:
> Hi,

> I'd like to use an Excel VB Macro to insert a new record into a SQL table
on a
> remote SQL Server. The record inserted would use values from fields in a
data
> entry template in an Excel spreadsheet. Has anybody successfully done
this?
> Does it require the client machine to have any special registered dll's? I
> thought I was on the right track with the code below, but it's not
working... I
> don't have the objects defined correctly. Any hints/suggestions? If
anybody has
> any sample code I would very much appreciate it! By the way, the SQL
Server is
> running SQL Server 6.50.479, on running Windows NT 4.0 SP6 (Build 1381),
with
> MDAC 2.6, IIS 4.0, twin Pentium 266's, and 384mb of RAM. Its ODBC
configuration
> allows access to the SQL databases via TCPIP or named pipes.

> Thanks,
> John Stanmeyer
> ----------

> Sub SubmitInfo()
> Dim dbConn As Object
> Dim Sql As String
> Set dbConn = CreateObject("ADODB.Connection")
> dbConn.Open "Driver={SQL Server}; Server=ServerName;

Database=testdatabase;

- Show quoted text -

Quote:
> Uid=sa; Pwd=;"
> Sql = "INSERT INTO Test_Table (Item_Type) VALUES('Test')"
> dbConn.Execute (Sql)
> dbConn.Close
> Set dbConn = Nothing
> End Sub



Sat, 06 Dec 2003 07:31:12 GMT  
 Excel/VB insert to remote SQL DB??
Jeff,

To clarify (and this is why cross-posts are a nuisance!), the code as-is
does execute out of Excel if MDAC etc. is installed and referenced
correctly.

Not to disagree with your suggestions in the least, but it sounds like
there is a more basic problem first and John is just starting out with this
stuff - unfortunately, I only copied sqlserver.programming on the response
below!

S.
.................................
John,

just fyi, ppl tend not to be quite so helpful if they see a post to so many
newsgrps - and this one is geared toward T-SQL programming, not VB/ADO
though the name is a bit misleading...

That said, there are two 'bridges' you have to cross, OLEDB/ODBC for
connectivity & ADO to do anything.

1 can be checked by using the ODBC applet in ctrl panel (just try to create
a machine datasource with default settings to yr DB and test connection)

2 requires yr code which works if the sa pwd is really blank (is it???), but
you have to make sure that
Tools->References in VB editor menu includes a check on Microsoft ActiveX
Data Objects Library, preferably 2.5 or 2.6. If it is not an option,
MSADO15.dll is not installed.

One other thing to consider - who is going to be responsible for verifying
the data that goes into the server...?

s.


Quote:
> Hi John,

> Which lines raise what errors?  Your code uses late binding.  Have you
> considered using early binding, and making a reference to the ADO object
> library?  You could then declare dbConn as a Connection instead of an
> object, and use the New keyword instead of create object.  It might be a
> little easier to debug, and autocomplete always helps.  Something like
this:

> Sub InsertData()
> Dim cnn As Connection
> Dim ConStr As String, Sql as String

>  ConStr = "Provider=sqloledb;Data Source=ServerName;Initial
> Catalog=Sales;User Id=sa;Password=;"
>  Sql = "INSERT INTO Test_Table (Item_Type) VALUES('Test')"

>  Set cnn = New ADODB.Connection

>  With cnn
>   .Open ConStr
>   .Execute(Sql)
>   .Close
>  End With

>  Set cnn = Nothing

> End Sub

> --
> Jeff McAhren
> Dallas, Texas



> > Hi,

> > I'd like to use an Excel VB Macro to insert a new record into a SQL
table
> on a
> > remote SQL Server. The record inserted would use values from fields in a
> data
> > entry template in an Excel spreadsheet. Has anybody successfully done
> this?
> > Does it require the client machine to have any special registered dll's?
I
> > thought I was on the right track with the code below, but it's not
> working... I
> > don't have the objects defined correctly. Any hints/suggestions? If
> anybody has
> > any sample code I would very much appreciate it! By the way, the SQL
> Server is
> > running SQL Server 6.50.479, on running Windows NT 4.0 SP6 (Build 1381),
> with
> > MDAC 2.6, IIS 4.0, twin Pentium 266's, and 384mb of RAM. Its ODBC
> configuration
> > allows access to the SQL databases via TCPIP or named pipes.

> > Thanks,
> > John Stanmeyer
> > ----------

> > Sub SubmitInfo()
> > Dim dbConn As Object
> > Dim Sql As String
> > Set dbConn = CreateObject("ADODB.Connection")
> > dbConn.Open "Driver={SQL Server}; Server=ServerName;
> Database=testdatabase;
> > Uid=sa; Pwd=;"
> > Sql = "INSERT INTO Test_Table (Item_Type) VALUES('Test')"
> > dbConn.Execute (Sql)
> > dbConn.Close
> > Set dbConn = Nothing
> > End Sub



Sat, 06 Dec 2003 08:50:13 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Excel/VB insert to remote SQL DB??

2. INSERT INTO Access DB from SQL DB-HELP!!

3. Updating remote SQL db with CSV file

4. Inserting embedded OLE objects into DB w/ SQL?

5. SQL: Inserting Date values From VB5 Into Access DB

6. HLP: inserting image into ODBC DB using SQL?

7. SQl Insert date into an Access DB

8. SQL Insert statement via ASP page to Access DB

9. How to Insert A String Variable into an SQL Statement using VBScript/ASP/Access db

10. insert image into sql db

11. Inserting HTML files into SQL DB /w VB6

12. can I insert an AutoNumber Field in Access DB with SQL

 

 
Powered by phpBB® Forum Software